Source code

001/*
002 *  Copyright 2010 Anyware Services
003 *
004 *  Licensed under the Apache License, Version 2.0 (the "License");
005 *  you may not use this file except in compliance with the License.
006 *  You may obtain a copy of the License at
007 *
008 *      http://www.apache.org/licenses/LICENSE-2.0
009 *
010 *  Unless required by applicable law or agreed to in writing, software
011 *  distributed under the License is distributed on an "AS IS" BASIS,
012 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 *  See the License for the specific language governing permissions and
014 *  limitations under the License.
015 */
016package org.ametys.plugins.forms.table;
017
018import java.io.IOException;
019import java.io.InputStream;
020import java.net.URISyntaxException;
021import java.sql.Connection;
022import java.sql.DatabaseMetaData;
023import java.sql.PreparedStatement;
024import java.sql.ResultSet;
025import java.sql.SQLException;
026import java.sql.Timestamp;
027import java.sql.Types;
028import java.util.ArrayList;
029import java.util.Collections;
030import java.util.HashMap;
031import java.util.LinkedHashMap;
032import java.util.List;
033import java.util.Locale;
034import java.util.Map;
035import java.util.Map.Entry;
036import java.util.Set;
037
038import org.apache.avalon.framework.component.Component;
039import org.apache.avalon.framework.service.ServiceException;
040import org.apache.avalon.framework.service.ServiceManager;
041import org.apache.avalon.framework.service.Serviceable;
042import org.apache.cocoon.ProcessingException;
043import org.apache.commons.lang.StringUtils;
044import org.slf4j.LoggerFactory;
045
046import org.ametys.cms.repository.Content;
047import org.ametys.core.datasource.ConnectionHelper;
048import org.ametys.core.datasource.dbtype.SQLDatabaseTypeExtensionPoint;
049import org.ametys.core.ui.Callable;
050import org.ametys.core.util.I18nUtils;
051import org.ametys.core.util.URIUtils;
052import org.ametys.plugins.forms.Field;
053import org.ametys.plugins.forms.Field.FieldType;
054import org.ametys.plugins.forms.Form;
055import org.ametys.plugins.forms.FormsException;
056import org.ametys.plugins.forms.data.FieldValue;
057import org.ametys.plugins.forms.data.UserEntry;
058import org.ametys.plugins.forms.jcr.FormPropertiesManager;
059import org.ametys.plugins.repository.AmetysObjectResolver;
060import org.ametys.plugins.workflow.store.JdbcWorkflowStore;
061import org.ametys.runtime.config.Config;
062import org.ametys.runtime.i18n.I18nizableText;
063
064/**
065 * Class that handles creation and deletion of a table used by a form.
066 */
067public class FormTableManager implements Component, Serviceable
068{
069    /** Avalon Role */
070    public static final String ROLE = FormTableManager.class.getName();
071    
072    /** The id of the configuration parameter used for the forms' datasource */
073    public static final String FORMS_POOL_CONFIG_PARAM = "plugins.forms.datasource";
074    
075    /** Prefix for database tables */
076    public static final String TABLE_PREFIX = "Forms_";
077    
078    /** ID field name. */
079    public static final String ID_FIELD = "id";
080    
081    /** Creation date field name. */
082    public static final String CREATION_DATE_FIELD = "creationDate";
083    
084    /** Workflow id column name. */
085    public static final String WORKFLOW_ID_FIELD = "workflowId"; 
086    
087    /** Suffix for filename column. */
088    public static final String FILE_NAME_COLUMN_SUFFIX = "-filename";
089    
090    /** Constant for table state */
091    public static final int TABLE_CREATED_AND_UP_TO_DATE = 2;
092    
093    /** Constant for table state */
094    public static final int TABLE_CREATED_BUT_NEED_UPDATE = 3;
095    
096    /** Constant for table state */
097    public static final int TABLE_NOT_CREATED = 1;
098    
099    /** Constant for table state */
100    public static final int TABLE_UNKOWN_STATUS = 0;
101
102    private static org.slf4j.Logger __logger = LoggerFactory.getLogger(FormTableManager.class);
103
104    private ServiceManager _manager;
105    
106    private FormPropertiesManager _formPropertiesManager;
107    private AmetysObjectResolver _resolver;
108    private I18nUtils _i18nUtils;
109    private JdbcWorkflowStore _jdbcWorkflowStore;
110    private SQLDatabaseTypeExtensionPoint _sqlDatabaseTypeExtensionPoint;
111    
112    @Override
113    public void service(ServiceManager smanager) throws ServiceException
114    {
115        _manager = smanager;
116    }
117    
118    private SQLDatabaseTypeExtensionPoint getSQLDatabaseTypeExtensionPoint()
119    {
120        if (_sqlDatabaseTypeExtensionPoint == null)
121        {
122            try
123            {
124                _sqlDatabaseTypeExtensionPoint = (SQLDatabaseTypeExtensionPoint) _manager.lookup(SQLDatabaseTypeExtensionPoint.ROLE);
125            }
126            catch (ServiceException e)
127            {
128                throw new RuntimeException(e);
129            }
130        }
131        return _sqlDatabaseTypeExtensionPoint;
132    }
133    
134    private I18nUtils getI18nUtils()
135    {
136        if (_i18nUtils == null)
137        {
138            try
139            {
140                _i18nUtils = (I18nUtils) _manager.lookup(I18nUtils.ROLE);
141            }
142            catch (ServiceException e)
143            {
144                throw new RuntimeException(e);
145            }
146        }
147        return _i18nUtils;
148    }
149    
150    private AmetysObjectResolver getAmetysObjectResolver()
151    {
152        if (_resolver == null)
153        {
154            try
155            {
156                _resolver = (AmetysObjectResolver) _manager.lookup(AmetysObjectResolver.ROLE);
157            }
158            catch (ServiceException e)
159            {
160                throw new RuntimeException(e);
161            }
162        }
163        return _resolver;
164    }
165    
166    private FormPropertiesManager getFormPropertiesManager()
167    {
168        if (_formPropertiesManager == null)
169        {
170            try
171            {
172                _formPropertiesManager = (FormPropertiesManager) _manager.lookup(FormPropertiesManager.ROLE);
173            }
174            catch (ServiceException e)
175            {
176                throw new RuntimeException(e);
177            }
178        }
179        return _formPropertiesManager;
180    }  
181    
182    private JdbcWorkflowStore getJdbcWorkflowStore()
183    {
184        if (_jdbcWorkflowStore == null)
185        {
186            try
187            {
188                _jdbcWorkflowStore = (JdbcWorkflowStore) _manager.lookup(JdbcWorkflowStore.ROLE);
189            }
190            catch (ServiceException e)
191            {
192                throw new RuntimeException(e);
193            }
194        }
195        return _jdbcWorkflowStore;
196    }
197    
198    /**
199     * Create database table to store results of content forms
200     * @param form informations used to create table
201     * @return true if the table was created false else
202     */
203    public boolean createTable(Form form)
204    {
205        if (form == null)
206        {
207            throw new IllegalArgumentException("Form object can not be null");
208        }
209        
210        Map<String, FieldValue> columns = getColumns(form);
211        if (!_createOrUpdateTable(form.getId(), columns))
212        {
213            return false;
214        }
215        return true;
216    }
217    
218    /**
219     * Drop the table by its name
220     * @param table the name of the table to drop
221     * @return true if the table was dropped, false otherwise
222     */
223    public boolean dropTable(String table)
224    {
225        PreparedStatement stmt = null;
226        Connection connection = null;
227        
228        try
229        {
230            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
231            connection = ConnectionHelper.getConnection(dataSourceId);
232            
233            String request = "DROP TABLE " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(ConnectionHelper.getDatabaseType(connection), table);
234            
235            stmt = connection.prepareStatement(request); 
236            stmt.executeUpdate();
237        }
238        catch (SQLException e)
239        {
240            __logger.error("Error while deleting table " + table, e);
241            return false;
242        }
243        finally
244        {
245            ConnectionHelper.cleanup(stmt);
246            ConnectionHelper.cleanup(connection);
247        }
248
249        return true;
250    }
251    
252    /**
253     * Get all the table names
254     * @return the list of all the forms' table names
255     */
256    public List<String> getTableNames()
257    {
258        List<String> formsTableNames = new ArrayList<> ();
259        Connection con = null;
260        PreparedStatement stmt = null;
261        DatabaseMetaData metadata = null;
262        ResultSet tables = null;
263
264        try
265        {
266            // Use a connection pool to establish a connection to the data base
267            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
268            con = ConnectionHelper.getConnection(dataSourceId);
269            metadata = con.getMetaData();
270            tables = metadata.getTables(null, null, "%", null);
271            
272            while (tables.next()) 
273            {
274                String tableName = tables.getString(3);
275                if (tableName.startsWith(TABLE_PREFIX))
276                {
277                    formsTableNames.add(tableName);
278                }
279            }
280        }
281        catch (SQLException e)
282        {
283            __logger.error("Error while retrieving the forms table names", e);
284        }
285        finally
286        {
287            // Clean up connection resources
288            ConnectionHelper.cleanup(stmt);
289            ConnectionHelper.cleanup(con);
290        }
291        
292        return formsTableNames;
293    }
294    
295    /**
296     * Get all the submissions for a form.
297     * @param form the form object.
298     * @param columns the columns
299     * @param offset The number of results to ignore.
300     * @param length The maximum number of results to return
301     * @param entryIds the ids of the submissions to retrieve
302     * @return the list of submissions.
303     * @throws FormsException if an error occurs.
304     */
305    public List<UserEntry> getSubmissions(Form form, Map<String, FieldValue> columns, int offset, int length, List<Integer> entryIds) throws FormsException
306    {
307        List<UserEntry> entries = new ArrayList<>();
308        
309        final String tableName = TABLE_PREFIX + form.getId();
310        
311        Connection connection = null;
312        PreparedStatement stmt = null;
313        ResultSet rs = null;
314        
315        try
316        {
317
318            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
319            connection = ConnectionHelper.getConnection(dataSourceId);
320            String dbType = ConnectionHelper.getDatabaseType(connection);
321            
322            String request = _getSubmissionsQuery(offset, length, tableName, dbType);
323            
324            stmt = connection.prepareStatement(request);
325            
326            // Execute the query.
327            rs = stmt.executeQuery();
328            
329            // Extract the results.
330            while (rs.next())
331            {
332                // First get the ID and submission date.
333                int id = rs.getInt(ID_FIELD);
334                Timestamp creationDate = rs.getTimestamp(CREATION_DATE_FIELD);
335                
336                List<FieldValue> entryValues = new ArrayList<>();
337                
338                for (Map.Entry<String, FieldValue> column : columns.entrySet())
339                {
340                    String columnLabel = column.getKey();
341                    FieldValue columnValue = column.getValue();
342                    
343                    // Extract the value.
344                    FieldValue value = null;
345                    
346                    switch (columnValue.getType())
347                    {
348                        case Types.VARCHAR:
349                        case Types.LONGVARCHAR:
350                            String sValue = rs.getString(columnLabel);
351                            value = new FieldValue(columnValue);
352                            value.setValue(sValue);
353                            entryValues.add(value);
354                            break;
355                        case Types.BOOLEAN:
356                            Boolean bValue = rs.getBoolean(columnLabel);
357                            value = new FieldValue(columnValue);
358                            value.setValue(bValue);
359                            entryValues.add(value);
360                            break;
361                        case Types.BLOB:
362                            String fileNameColumn = columnLabel + FILE_NAME_COLUMN_SUFFIX;
363                            String normalizedName = DbTypeHelper.normalizeName(dbType, fileNameColumn);
364                            String fileName = rs.getString(normalizedName);
365                            value = new FieldValue(columnValue);
366                            
367                            try (InputStream isValue = _sqlDatabaseTypeExtensionPoint.getBlob(dbType, rs, columnLabel))
368                            {
369                                value.setValue(isValue == null ? null : fileName);
370                            }
371                            
372                            entryValues.add(value);
373                            break;
374                        case Types.INTEGER:
375                            Integer iValue = rs.getInt(columnLabel);
376                            value = new FieldValue(columnValue);
377                            value.setValue(iValue);
378                            entryValues.add(value);
379                            break;
380                        default:
381                            break;
382                    }
383                }
384                
385                Integer workflowId = null;
386                if (hasWorkflowIdColumn(form.getId()))
387                {
388                    workflowId = rs.getInt(WORKFLOW_ID_FIELD);
389                }
390
391                if (entryIds == null || entryIds.contains(id))
392                {
393                    UserEntry entry = new UserEntry(id, creationDate, entryValues, workflowId);
394                    entries.add(entry);
395                }
396            }
397        }
398        catch (SQLException | IOException e)
399        {
400            __logger.error("Error while getting entries for table " + tableName, e);
401            throw new FormsException("Error while getting entries for table " + tableName, e);
402        }
403        finally
404        {
405            ConnectionHelper.cleanup(rs);
406            ConnectionHelper.cleanup(stmt);
407            ConnectionHelper.cleanup(connection);
408        }
409        
410        return entries;
411    }
412
413    /**
414     * Get the submissions query
415     * @param offset The number of results to ignore.
416     * @param length The maximum number of results to return
417     * @param tableName the name of the table
418     * @param dbType the type of the database used
419     * @return the string query for getting the submissions of a form
420     */
421    private String _getSubmissionsQuery(int offset, int length, final String tableName, String dbType)
422    {
423        return getSQLDatabaseTypeExtensionPoint().languageLimitQuery(dbType, "SELECT * FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName), Integer.toString(length), Integer.toString(offset));
424    }
425    
426    /**
427     * Get the total count of submissions for a form.
428     * @param formId the id of the form
429     * @return the total count of submissions, or -1 if an error occurs.
430     * @throws FormsException if an error occurs.
431     */
432    public int getTotalSubmissions(String formId) throws FormsException
433    {
434        final String tableName = TABLE_PREFIX + formId;
435        
436        Connection connection = null;
437        PreparedStatement stmt = null;
438        ResultSet rs = null;
439        
440        try
441        {
442
443            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
444            connection = ConnectionHelper.getConnection(dataSourceId);
445            
446            String dbType = ConnectionHelper.getDatabaseType(connection);
447            
448            String request = "SELECT COUNT(*) FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName);
449            
450            stmt = connection.prepareStatement(request);
451            
452            // Execute the query.
453            rs = stmt.executeQuery();
454            
455            // Extract the result.
456            if (rs.next())
457            {
458                int count = rs.getInt(1);
459                return count;
460            }
461        }
462        catch (SQLException e)
463        {
464            __logger.error("Error while getting entries for table " + tableName, e);
465            throw new FormsException("Error while getting entries for table " + tableName, e);
466        }
467        finally
468        {
469            ConnectionHelper.cleanup(rs);
470            ConnectionHelper.cleanup(stmt);
471            ConnectionHelper.cleanup(connection);
472        }
473        
474        return -1;
475    }
476    
477    /**
478     * Get the forms of contents
479     * @param contentIds The ids of contents
480     * @param currentLanguage The current language
481     * @return The forms
482     * @throws FormsException if an error occurred while getting content' form
483     * @throws URISyntaxException if an error occurred when URL encoding form's label
484     */
485    @Callable
486    public List<Map<String, Object>> getContentForms (List<String> contentIds, String currentLanguage) throws FormsException, URISyntaxException
487    {
488        List<Map<String, Object>> result = new ArrayList<>();
489        
490        for (String contentId : contentIds)
491        {
492            Content content = getAmetysObjectResolver().resolveById(contentId);
493            
494            Map<String, Object> contentforms = new HashMap<>();
495            
496            contentforms.put("id", content.getId());
497            contentforms.put("title", content.getTitle(new Locale(currentLanguage)));
498            
499            List<Map<String, Object>> forms2json = new ArrayList<>();
500            
501            List<Form> forms = getFormPropertiesManager().getForms(content);
502            for (Form form : forms)
503            {
504                Map<String, Object> form2json = new HashMap<>();
505                form2json.put("id", form.getId());
506                form2json.put("label", URIUtils.decode(form.getLabel()));
507                form2json.put("workflowName", StringUtils.defaultString(form.getWorkflowName(), ""));
508                
509                forms2json.add(form2json);
510            }
511            
512            contentforms.put("forms", forms2json);
513            
514            result.add(contentforms);
515        }
516        
517        return result;
518    }
519    
520    /**
521     * Get the columns information of a form
522     * @param siteName The site name
523     * @param formId the identifier of form
524     * @return The columns
525     * @throws FormsException if an error occurred
526     */
527    @Callable
528    public List<Map<String, Object>> getColumns (String siteName, String formId) throws FormsException
529    {
530        List<Map<String, Object>> columns2json = new ArrayList<>();
531        
532        Form form = getFormPropertiesManager().getForm(siteName, formId);
533        
534        Map<String, FieldValue> columns = getColumns(form);
535        for (FieldValue column : columns.values())
536        {
537            Map<String, Object> column2json = new HashMap<>();
538            column2json.put("id", column.getColumnName());
539            
540            Field field = column.getField();
541            column2json.put("label", field.getLabel());
542            column2json.put("type", field.getType().toString());
543            column2json.put("name", field.getName());
544            column2json.put("properties", field.getProperties());
545            
546            columns2json.add(column2json);
547        }
548        
549        return columns2json;
550    }
551    
552    /**
553     * Remove a list of tables
554     * @param tableNames the names of the tables to delete
555     * @throws FormsException if the drop of the tables is not successful
556     */
557    @Callable
558    public void removeTables(List<String> tableNames) throws FormsException
559    {
560        for (String tableName : tableNames)
561        {
562            if (!dropTable(tableName))
563            {
564                throw new FormsException("An error occurred occured while removing the tables from the database.");
565            }
566        }
567    }
568    
569    /**
570     * Form to tableinfo.
571     * @param form the form.
572     * @return the tableinfo.
573     */
574    public Map<String, FieldValue> getColumns(Form form)
575    {
576        Map<String, FieldValue> columns = new LinkedHashMap<>();
577        
578        for (Field field : form.getFields())
579        {
580            final String id = field.getId();
581            final String name = field.getName();
582            
583            FieldValue fdValue = null;
584            
585            switch (field.getType())
586            {
587                case TEXT:
588                case HIDDEN:
589                case PASSWORD:
590                case SELECT:
591                    fdValue = new FieldValue(id, Types.VARCHAR, null, field);
592                    columns.put(id, fdValue);
593                    break;
594                case TEXTAREA:
595                    fdValue = new FieldValue(id, Types.LONGVARCHAR, null, field);
596                    columns.put(id, fdValue);
597                    break;
598                case RADIO:
599                    if (!columns.containsKey(name))
600                    {
601                        String value = field.getProperties().get("value");
602                        String label = field.getLabel();
603                        int index = 1;
604                        field.getProperties().put("size", String.valueOf(index));
605                        field.getProperties().put("option-" + index + "-value", value);
606                        field.getProperties().put("option-" + index + "-label", label);
607                        
608                        field.getProperties().remove("value");
609                        
610                        fdValue = new FieldValue(name, Types.VARCHAR, null, field);
611                        columns.put(name, fdValue);
612                    }
613                    else
614                    {
615                        // The value exists, clone it, concatenating the label.
616                        if (StringUtils.isNotEmpty(field.getLabel()))
617                        {
618                            Field radioField = columns.get(name).getField();
619                            int index = Integer.parseInt(radioField.getProperties().get("size"));
620                            index++;
621                            
622                            String value = field.getProperties().get("value");
623                            String label = field.getLabel();
624                            
625                            radioField.getProperties().put("size", String.valueOf(index));
626                            radioField.getProperties().put("option-" + index + "-value", value);
627                            radioField.getProperties().put("option-" + index + "-label", label);
628                            
629                            Field dummyField = new Field(radioField.getId(), radioField.getType(), radioField.getName(), radioField.getLabel() + "/" + field.getLabel(), radioField.getProperties());
630                            columns.get(name).setField(dummyField);
631                        }
632                    }
633                    break;
634                case CHECKBOX:
635                    fdValue = new FieldValue(id, Types.BOOLEAN, null, field);
636                    columns.put(id, fdValue);
637                    break;
638                case FILE:
639                    fdValue = new FieldValue(id, Types.BLOB, null, field);
640                    columns.put(id, fdValue);
641                    break;
642                case CAPTCHA:
643                    break;
644                default:
645                    break;
646            }
647            
648        }
649        
650        return columns;
651    }
652    
653    /**
654     * Get the full column type corresponding to the database type (with precision).
655     * @param sqlType the sqltype value.
656     * @param dbType the database type.
657     * @return the real column type identifier (to be included in CREATE statement).
658     */
659    protected String _getColumnType(int sqlType, String dbType)
660    {
661        switch (sqlType)
662        {
663            case Types.LONGVARCHAR:
664                return  DbTypeHelper.getTextType(dbType);
665            case Types.BOOLEAN:
666                return  DbTypeHelper.getBooleanType(dbType);
667            case Types.BLOB:
668                return  DbTypeHelper.getBinaryType(dbType);
669            case Types.VARCHAR:
670            default:
671                return  DbTypeHelper.getVarcharType(dbType);
672        }
673    }
674    
675    /**
676     * Get the column type name (without precision) corresponding to the SQL type.
677     * @param sqlType the SQL type value.
678     * @param dbType the database type.
679     * @return the real column type name (without precision).
680     */
681    protected String _getColumnTypeName(int sqlType, String dbType)
682    {
683        return StringUtils.substringBefore(_getColumnType(sqlType, dbType), "(");
684    }
685    
686    /**
687     * Test if two columns have the same type.
688     * @param column the column retrieved from the database.
689     * @param newColumn the new column.
690     * @param dbType the database type.
691     * @return true if the two columns have the same type.
692     */
693    protected boolean _isSameType(DbColumn column, FieldValue newColumn, String dbType)
694    {
695        int newColumnType = newColumn.getType();
696        int currentColumnType = column.getSqlType();
697        String newColumnTypeName = _getColumnTypeName(newColumnType, dbType);
698        String currentColumnTypeName = column.getTypeName();
699        
700        return currentColumnType == newColumnType || currentColumnTypeName.equals(newColumnTypeName);
701    }
702    
703    private boolean _createTable(String formId, Map<String, FieldValue> columns)
704    {
705        String tableName = TABLE_PREFIX + formId;
706        
707        Connection connection = null;
708        PreparedStatement stmt = null;
709        
710        try
711        {
712
713            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
714            connection = ConnectionHelper.getConnection(dataSourceId);
715            
716            String dbType = ConnectionHelper.getDatabaseType(connection);
717            StringBuilder sql = new StringBuilder();
718            
719            sql.append("CREATE TABLE ");
720            sql.append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
721            sql.append(" ( ").append(ID_FIELD).append(" ").append(DbTypeHelper.getIdentityType(dbType)).append(" ").append(DbTypeHelper.getIdentityMarker(dbType)).append(", ");
722            sql.append(CREATION_DATE_FIELD).append(" ").append(DbTypeHelper.getDateTimeType(dbType)).append(" NOT NULL,");
723            
724            for (Map.Entry<String, FieldValue> column : columns.entrySet())
725            {
726                int sqlType = column.getValue().getType();
727                sql.append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, column.getKey())).append(" ");
728                sql.append(_getColumnType(sqlType, dbType));
729                sql.append(" DEFAULT NULL,");
730                
731                // Add a column for the file name.
732                if (sqlType == Types.BLOB)
733                {
734                    String fileNameColumn = column.getKey() + FILE_NAME_COLUMN_SUFFIX;
735                    String normalizedName = DbTypeHelper.normalizeName(dbType, fileNameColumn);
736                    
737                    sql.append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, normalizedName)).append(" ");
738                    sql.append(DbTypeHelper.getVarcharType(dbType));
739                    sql.append(" DEFAULT NULL,");
740                }
741            }
742            
743            sql.append("PRIMARY KEY (").append(ID_FIELD).append("))");
744            
745            if (__logger.isDebugEnabled())
746            {
747                __logger.debug("Creating table : " + sql.toString());
748            }
749            
750            stmt = connection.prepareStatement(sql.toString());
751            stmt.executeUpdate();
752            ConnectionHelper.cleanup(stmt);
753            
754            // create sequence for oracle database
755            if (ConnectionHelper.DATABASE_ORACLE.equals(dbType))
756            {
757                String sqlQuery = "create sequence seq_" + formId; 
758                stmt = connection.prepareStatement(sqlQuery);
759                stmt.executeUpdate();
760            }
761            
762            return true;
763        }
764        catch (SQLException e)
765        {
766            __logger.error("Unable to create table " + tableName, e);
767            return false;
768        }
769        finally
770        {
771            ConnectionHelper.cleanup(stmt);
772            ConnectionHelper.cleanup(connection);
773        }
774    }
775    
776    private boolean _alterTable(String formId, Map<String, FieldValue> newColumns)
777    {
778        String tableName = TABLE_PREFIX + formId;
779        
780        Connection connection = null;
781        String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
782        
783        try
784        {
785            connection = ConnectionHelper.getConnection(dataSourceId);
786            
787            // Start transaction.
788            connection.setAutoCommit(false);
789            
790            // Get the existing columns.
791            Map<String, DbColumn> existingColumns = _getExistingColumns(connection, tableName);
792            
793            // Compute the modifications to make.
794            DbColumnModifications modifications = _getColumnsToModify(connection, existingColumns, newColumns);
795            
796            // Move the columns that were deleted or replaced.
797            _moveColumns(connection, modifications.getColumnsToRemove(), existingColumns, tableName);
798            
799            // Add the new columns.
800            _addColumns(connection, modifications.getColumnsToAdd(), tableName);
801            
802            // Commit the transaction.
803            connection.commit();
804        }
805        catch (SQLException e)
806        {
807            __logger.error("Unable to alter table " + tableName, e);
808            try
809            {
810                connection.rollback();
811            }
812            catch (SQLException sqlex)
813            {
814                // Ignore.
815                __logger.error("Error rollbacking the 'alter table' statements for table " + tableName, e);
816            }
817            return false;
818        }
819        finally
820        {
821            ConnectionHelper.cleanup(connection);
822        }
823        return true;
824        
825    }
826    
827    private int _checkTableStatus(String formId, Map<String, FieldValue> newColumns)
828    {
829        String tableName = TABLE_PREFIX + formId;
830        
831        Connection connection = null;
832        ResultSet tables = null;
833        ResultSet rs = null;
834        
835        Map<String, DbColumn> currentColumns = new HashMap<>();
836        
837        try
838        {
839
840            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
841            connection = ConnectionHelper.getConnection(dataSourceId);
842            
843            String dbType = ConnectionHelper.getDatabaseType(connection);
844            
845            tableName = DbTypeHelper.filterName(dbType, tableName);
846            
847            DatabaseMetaData metadata = connection.getMetaData();
848            tables = metadata.getTables(connection.getCatalog(), null, tableName, null);
849            
850            if (!tables.next())
851            {
852                return TABLE_NOT_CREATED;
853            }
854            
855            currentColumns = _getExistingColumns(connection, tableName);
856            
857            for (String newColumn : newColumns.keySet())
858            {
859                String filteredColumn = DbTypeHelper.filterName(dbType, newColumn);
860                
861                // Test the existence of the column.
862                if (!currentColumns.containsKey(filteredColumn))
863                {
864                    return TABLE_CREATED_BUT_NEED_UPDATE;
865                }
866                
867                // Test that the column type has not changed.
868                DbColumn currentColumn = currentColumns.get(filteredColumn);
869                FieldValue newColumnFv = newColumns.get(newColumn);
870                
871                if (!_isSameType(currentColumn, newColumnFv, dbType))
872                {
873                    return TABLE_CREATED_BUT_NEED_UPDATE;
874                }
875            }
876            
877            return TABLE_CREATED_AND_UP_TO_DATE;
878        }
879        catch (SQLException e)
880        {
881            __logger.error("Unable to get columns from table " + tableName, e);
882            return TABLE_UNKOWN_STATUS;
883        }
884        finally
885        {
886            ConnectionHelper.cleanup(tables);
887            ConnectionHelper.cleanup(rs);
888            ConnectionHelper.cleanup(connection);
889        }
890    }
891
892    private boolean _createOrUpdateTable(String formId, final Map<String, FieldValue> columns)
893    {
894        boolean toReturn = true;
895        switch (_checkTableStatus(formId, columns))
896        {
897            case TABLE_NOT_CREATED:
898                if (!_createTable(formId, columns))
899                {
900                    toReturn = false;
901                }
902                break;
903            case TABLE_CREATED_BUT_NEED_UPDATE:
904                if (!_alterTable(formId, columns))
905                {
906                    toReturn = false;
907                }
908                break;
909            case TABLE_CREATED_AND_UP_TO_DATE:
910            case TABLE_UNKOWN_STATUS:
911            default:
912                break;
913        }
914        return toReturn;
915    }
916
917    private DbColumnModifications _getColumnsToModify(Connection con, Map<String, DbColumn> existingColumns, Map<String, FieldValue> newColumns)
918    {
919        DbColumnModifications modifications = new DbColumnModifications();
920        
921        String dbType = ConnectionHelper.getDatabaseType(con);
922        
923        Map<String, FieldValue> columnsToAdd = new LinkedHashMap<>();
924        Map<String, DbColumn> columnsToRemove = new HashMap<>();
925        for (Map.Entry<String, FieldValue> newColumn : newColumns.entrySet())
926        {
927            String filteredName = DbTypeHelper.filterName(dbType, newColumn.getKey());
928            columnsToAdd.put(filteredName, newColumn.getValue());
929        }
930        
931        if (existingColumns != null)
932        {
933            for (String existingColName : existingColumns.keySet())
934            {
935                DbColumn existingColumn = existingColumns.get(existingColName);
936                FieldValue newColumn = columnsToAdd.get(existingColName);
937                
938                // If the column does not already exist or if the type has changed,
939                // mark the current column to be removed and keep the new column in the "to be added" list.
940                if (newColumn != null && !_isSameType(existingColumn, newColumn, dbType))
941                {
942                    columnsToRemove.put(existingColName, existingColumn);
943                }
944                else
945                {
946                    // The column already exists and its type has not changed: do not touch this one.
947                    columnsToAdd.remove(existingColName);
948                }
949            }
950        }
951        
952        modifications.setColumnsToAdd(columnsToAdd);
953        modifications.setColumnsToRemove(columnsToRemove);
954        
955        return modifications;
956    }
957    
958    private Map<String, DbColumn> _getExistingColumns(Connection con, String table) throws SQLException
959    {
960        ResultSet columns = null;
961        Map<String, DbColumn> toReturn = new LinkedHashMap<>();
962        try
963        {
964            String dbType = ConnectionHelper.getDatabaseType(con);
965            String filteredTableName = DbTypeHelper.filterName(dbType, table);
966            
967            DatabaseMetaData metadata = con.getMetaData();
968            columns = metadata.getColumns(con.getCatalog(), null, filteredTableName, null);
969            while (columns.next())
970            {
971                String columnName = columns.getString("COLUMN_NAME");
972                Integer sqlType = columns.getInt("DATA_TYPE");
973                String typeName = columns.getString("TYPE_NAME");
974                Integer colSize = columns.getInt("COLUMN_SIZE");
975                
976                DbColumn col = new DbColumn(columnName, sqlType, typeName, colSize);
977                
978                toReturn.put(columnName, col);
979            }
980        }
981        catch (SQLException e)
982        {
983            __logger.error("Unable to get columns from " + table, e);
984            throw e;
985        }
986        finally
987        {
988            ConnectionHelper.cleanup(columns);
989        }
990        return toReturn;
991    }
992    
993    private void _moveColumns(Connection con, Map<String, DbColumn> columnsToRemove, Map<String, DbColumn> existingColumns, String tableName) throws SQLException
994    {
995        String dbType = ConnectionHelper.getDatabaseType(con);
996        PreparedStatement stmt = null;
997        
998        Set<String> existingColumnNames = existingColumns.keySet();
999        
1000        try
1001        {
1002            for (String columnName : columnsToRemove.keySet())
1003            {
1004                DbColumn columnToRemove = columnsToRemove.get(columnName);
1005                
1006                String newName = _getNewName(con, columnName, existingColumnNames);
1007                
1008                String sql = DbTypeHelper.getRenameColumnStatement(tableName, columnToRemove, newName, dbType, getSQLDatabaseTypeExtensionPoint());
1009                
1010                if (__logger.isDebugEnabled())
1011                {
1012                    __logger.debug("Moving column: " + sql);
1013                }
1014                
1015                stmt = con.prepareStatement(sql);
1016                stmt.executeUpdate();
1017                
1018                // Add a column for the file name.
1019                if (columnToRemove.getSqlType() == Types.BLOB)
1020                {
1021                    // Release the previous statement.
1022                    ConnectionHelper.cleanup(stmt);
1023                    
1024                    String fileNameColumn = columnName + FILE_NAME_COLUMN_SUFFIX;
1025                    String newFileNameColumn = _getNewName(con, fileNameColumn, existingColumnNames);
1026                    String varcharType = DbTypeHelper.getVarcharType(dbType);
1027                    
1028                    sql = DbTypeHelper.getRenameColumnStatement(tableName, fileNameColumn, newFileNameColumn, varcharType, dbType, getSQLDatabaseTypeExtensionPoint());
1029                    
1030                    if (__logger.isDebugEnabled())
1031                    {
1032                        __logger.debug("Altering table : " + sql.toString());
1033                    }
1034                    
1035                    stmt = con.prepareStatement(sql.toString());
1036                    stmt.executeUpdate();
1037                }
1038            }
1039        }
1040        catch (SQLException e)
1041        {
1042            ConnectionHelper.cleanup(stmt);
1043            throw e;
1044        }
1045    }
1046    
1047    private void _addColumns(Connection con, Map<String, FieldValue> columnsToAdd, String tableName) throws SQLException
1048    {
1049        String dbType = ConnectionHelper.getDatabaseType(con);
1050        PreparedStatement stmt = null;
1051        
1052        try
1053        {
1054            for (Entry<String, FieldValue> column : columnsToAdd.entrySet())
1055            {
1056                StringBuilder sql = new StringBuilder();
1057                
1058                sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1059                
1060                int sqlType = column.getValue().getType();
1061                
1062                sql.append(" ADD ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, column.getKey())).append(" ");
1063                sql.append(_getColumnType(sqlType, dbType));
1064                sql.append(" DEFAULT NULL");
1065                
1066                if (__logger.isDebugEnabled())
1067                {
1068                    __logger.debug("Altering table : " + sql.toString());
1069                }
1070                
1071                stmt = con.prepareStatement(sql.toString());
1072                stmt.executeUpdate();
1073                
1074                // Add a column for the file name.
1075                if (sqlType == Types.BLOB)
1076                {
1077                    // Release the previous statement.
1078                    ConnectionHelper.cleanup(stmt);
1079                    
1080                    sql.setLength(0);
1081                    sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1082                    
1083                    String fileNameColumn = column.getKey() + FILE_NAME_COLUMN_SUFFIX;
1084                    String normalizedName = DbTypeHelper.normalizeName(dbType, fileNameColumn);
1085                    
1086                    sql.append(" ADD ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, normalizedName)).append(" ");
1087                    sql.append(DbTypeHelper.getVarcharType(dbType));
1088                    sql.append(" DEFAULT NULL");
1089                    
1090                    if (__logger.isDebugEnabled())
1091                    {
1092                        __logger.debug("Adding column: " + sql.toString());
1093                    }
1094                    
1095                    stmt = con.prepareStatement(sql.toString());
1096                    stmt.executeUpdate();
1097                }
1098            }
1099        }
1100        catch (SQLException e)
1101        {
1102            ConnectionHelper.cleanup(stmt);
1103            throw e;
1104        }
1105    }
1106    
1107    /**
1108     * Get the new name of a column to be moved.
1109     * @param con the connection.
1110     * @param currentName the current name.
1111     * @param existingColumnNames the names of the existing columns
1112     * @return the new name.
1113     */
1114    private String _getNewName(Connection con, String currentName, Set<String> existingColumnNames)
1115    {
1116        String dbType = ConnectionHelper.getDatabaseType(con);
1117        
1118        int i = 1;
1119        String newName = DbTypeHelper.normalizeName(dbType, currentName + "_old" + i);
1120        String filteredNewName = DbTypeHelper.filterName(dbType, newName);
1121        
1122        while (existingColumnNames.contains(filteredNewName))
1123        {
1124            i++;
1125            newName = DbTypeHelper.normalizeName(dbType, currentName + "_old" + i);
1126            filteredNewName = DbTypeHelper.filterName(dbType, newName);
1127        }
1128        
1129        return newName;
1130    }
1131    
1132    /**
1133     * Get the SQL type corresponding to a field type.
1134     * @param fieldType the field
1135     * @return the sql type as indicated in {@link java.sql.Types}.
1136     */
1137    public static int getFieldSqlType(FieldType fieldType)
1138    {
1139        int sqlType = Types.VARCHAR;
1140        switch (fieldType)
1141        {
1142            case TEXT:
1143            case HIDDEN:
1144            case PASSWORD:
1145            case SELECT:
1146            case RADIO:
1147                sqlType = Types.VARCHAR;
1148                break;
1149            case TEXTAREA:
1150                sqlType = Types.LONGVARCHAR;
1151                break;
1152            case CHECKBOX:
1153                sqlType = Types.BOOLEAN;
1154                break;
1155            case FILE:
1156                sqlType = Types.BLOB;
1157                break;
1158            case CAPTCHA:
1159                sqlType = Types.OTHER;
1160                break;
1161            default:
1162                break;
1163        }
1164        return sqlType;
1165    }
1166    
1167    /**
1168     * Add a workflow id column to the given table
1169     * @param formId the id of the form to alter
1170     * @throws FormsException if an error occurs
1171     */
1172    public void addWorkflowIdColumn(String formId) throws FormsException
1173    {
1174        final String tableName = TABLE_PREFIX + formId;
1175        
1176        Connection con = null;
1177        PreparedStatement stmt = null;
1178        
1179        try
1180        {
1181            // Connect to the database.
1182            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1183            con = ConnectionHelper.getConnection(dataSourceId);
1184            String dbType = ConnectionHelper.getDatabaseType(con);
1185            StringBuilder sql = new StringBuilder();
1186
1187            sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1188            sql.append(" ADD ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, WORKFLOW_ID_FIELD)).append(" ");
1189            sql.append(DbTypeHelper.getIntegerType(dbType));
1190
1191            if (__logger.isDebugEnabled())
1192            {
1193                __logger.debug("Adding column: " + sql.toString());
1194            }
1195
1196            stmt = con.prepareStatement(sql.toString());
1197            stmt.executeUpdate();
1198        }
1199        catch (SQLException e)
1200        {
1201            __logger.error("Error while adding the workflow id column for the table " + tableName, e);
1202            throw new FormsException("Error while adding a column to the table " + tableName, e);
1203        }
1204        finally
1205        {
1206            // Clean up connection resources
1207            ConnectionHelper.cleanup(stmt);
1208            ConnectionHelper.cleanup(con);
1209        }
1210    }
1211    
1212    /**
1213     * Delete the workflow id column of the given table
1214     * @param formId the id of the form to alter
1215     * @throws FormsException if an error occurs
1216     */
1217    public void dropWorkflowIdColumn(String formId) throws FormsException
1218    {
1219        final String tableName = TABLE_PREFIX + formId;
1220        
1221        Connection con = null;
1222        PreparedStatement stmt = null;
1223        
1224        try
1225        {
1226            // Connect to the database.
1227            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1228            con = ConnectionHelper.getConnection(dataSourceId);
1229            String dbType = ConnectionHelper.getDatabaseType(con);
1230            StringBuilder sql = new StringBuilder();
1231
1232            sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1233            sql.append(" DROP COLUMN ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, WORKFLOW_ID_FIELD));
1234
1235            if (__logger.isDebugEnabled())
1236            {
1237                __logger.debug("Deleting column: " + sql.toString());
1238            }
1239
1240            stmt = con.prepareStatement(sql.toString());
1241            stmt.executeUpdate();
1242        }
1243        catch (SQLException e)
1244        {
1245            __logger.error("Error while deleting the workflow id column for the table " + tableName, e);
1246            throw new FormsException("Error while deleting a column from the table " + tableName, e);
1247        }
1248        finally
1249        {
1250            // Clean up connection resources
1251            ConnectionHelper.cleanup(stmt);
1252            ConnectionHelper.cleanup(con);
1253        }
1254    }
1255
1256    /**
1257     * Set a new workflow id to the column that has the given old workflow id
1258     * @param form the form
1259     * @param entryId the id of the entry
1260     * @param newWorkflowId the new workflow id
1261     * @throws FormsException if an error occurs
1262     */
1263    public void setWorkflowId(Form form, long entryId, long newWorkflowId) throws FormsException
1264    {
1265        final String tableName = TABLE_PREFIX + form.getId();
1266
1267        Connection con = null;
1268        PreparedStatement stmt = null;
1269        
1270        try
1271        {
1272            // Connect to the database.
1273            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1274            con = ConnectionHelper.getConnection(dataSourceId);
1275            String dbType = ConnectionHelper.getDatabaseType(con);
1276            
1277            String query = "UPDATE " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName);
1278            query += " SET "  + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, WORKFLOW_ID_FIELD) + " = ?";
1279            query += " WHERE " + ID_FIELD + " = ?";
1280
1281            stmt = con.prepareStatement(query);
1282
1283            stmt.setLong(1, newWorkflowId);
1284            stmt.setLong(2, entryId);
1285
1286            // Execute the query.
1287            stmt.executeUpdate();
1288        }
1289        catch (SQLException e)
1290        {
1291            __logger.error("Error while resetting the workflow id for the table " + tableName, e);
1292            throw new FormsException("Error while deleting entry for table " + tableName, e);
1293        }
1294        finally
1295        {
1296            // Clean up connection resources
1297            ConnectionHelper.cleanup(stmt);
1298            ConnectionHelper.cleanup(con);
1299        }
1300    }
1301    
1302    /**
1303     * Get the workflow id of the given form entry or of all the entries
1304     * @param formId the id of the form
1305     * @param entryId the entry to get the workflow id from. If null, all the workflow ids are returned
1306     * @throws FormsException if an error occurs.
1307     * @return the list of workflow ids
1308     */
1309    public List<Integer> getWorkflowIds(String formId, Integer entryId) throws FormsException
1310    {
1311        List<Integer> workflowIds = new ArrayList<> ();
1312
1313        final String tableName = TABLE_PREFIX + formId;
1314        
1315        Connection con = null;
1316        PreparedStatement stmt = null;
1317        ResultSet rs = null;
1318        
1319        try
1320        {
1321            if (!hasWorkflowIdColumn(formId))
1322            {
1323                return Collections.EMPTY_LIST;
1324            }
1325            
1326            // Connect to the database.
1327            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1328            con = ConnectionHelper.getConnection(dataSourceId);
1329            String dbType = ConnectionHelper.getDatabaseType(con);
1330            
1331            String query = "SELECT " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, "workflowId") + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName);
1332            if (entryId != null)
1333            {
1334                query += " WHERE " + ID_FIELD + " = ?";
1335            }
1336
1337            stmt = con.prepareStatement(query);
1338
1339            if (entryId != null)
1340            {
1341                stmt.setInt(1, entryId);
1342            }
1343            
1344            // Execute the query.
1345            rs = stmt.executeQuery();
1346
1347            // Extract the result(s).
1348            while (rs.next())
1349            {
1350                workflowIds.add(rs.getInt(1));
1351            }
1352        }
1353        catch (SQLException e)
1354        {
1355            __logger.error("Error while getting workflow ids from the table " + tableName, e);
1356            throw new FormsException("Error while getting workflow ids from the table " + tableName, e);
1357        }
1358        finally
1359        {
1360            // Clean up connection resources
1361            ConnectionHelper.cleanup(rs);
1362            ConnectionHelper.cleanup(stmt);
1363            ConnectionHelper.cleanup(con);
1364        }
1365
1366        return workflowIds;
1367    }
1368    
1369    /**
1370     * Does the given form have a workflow id column in its SQL table ?
1371     * @param formId the id of the form
1372     * @return true if the form has a workflow id column, false otherwise
1373     * @throws SQLException if a database access error occurs
1374     */
1375    public boolean hasWorkflowIdColumn (String formId) throws SQLException
1376    {
1377        boolean hasWorkflowId = true;
1378        
1379        Connection con = null;
1380        final String tableName = TABLE_PREFIX + formId;
1381        
1382        try
1383        {
1384            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1385            con = ConnectionHelper.getConnection(dataSourceId);
1386            Map<String, DbColumn> currentColumns = _getExistingColumns(con, tableName);        
1387            
1388            // Check if the column contains a workflow id
1389            if (!currentColumns.containsKey(WORKFLOW_ID_FIELD))
1390            {
1391                hasWorkflowId = false;
1392            }
1393            
1394            return hasWorkflowId;
1395        }
1396        finally 
1397        {
1398            ConnectionHelper.cleanup(con);
1399        }
1400    }
1401    
1402    /**
1403     * Delete a list of entries of a form
1404     * @param siteName The site name
1405     * @param formId The form id
1406     * @param entries The list of entries to delete
1407     * @throws ProcessingException if the given parameters are wrong
1408     * @throws FormsException if an error occurs when deleting the form submission
1409     */
1410    @Callable
1411    public void deleteEntry(String siteName, String formId, List<Integer> entries) throws ProcessingException, FormsException
1412    {
1413        if (StringUtils.isEmpty(siteName) || StringUtils.isEmpty(formId))
1414        {
1415            throw new ProcessingException("The site name and form ID must be provided.");
1416        }
1417    
1418        Form form = getFormPropertiesManager().getForm(siteName, formId);
1419        
1420        if (form == null)
1421        {
1422            throw new ProcessingException("The form of ID '" + formId + " can't be found in the site '" + siteName + "'.");
1423        }
1424        
1425        final String tableName = TABLE_PREFIX + form.getId();
1426        
1427        Connection connection = null;
1428        PreparedStatement stmt = null;
1429        
1430        try
1431        {
1432            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1433            connection = ConnectionHelper.getConnection(dataSourceId);
1434            String dbType = ConnectionHelper.getDatabaseType(connection);
1435            
1436            StringBuilder sb = new StringBuilder();
1437            sb.append("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName) + " WHERE ");
1438
1439            
1440            int i;
1441            int count = entries.size();
1442            for (i = 0; i < count; i++)
1443            {
1444                if (i > 0)
1445                {
1446                    sb.append(" OR ");
1447                }
1448                sb.append(ID_FIELD).append("=?");
1449            }
1450            
1451            stmt = connection.prepareStatement(sb.toString());
1452            
1453            i = 1;
1454            List<Integer> workflowIds = new ArrayList<> ();
1455            for (Integer entryId : entries)
1456            {
1457                workflowIds.addAll(getWorkflowIds(formId, entryId));
1458                stmt.setInt(i, entryId);
1459                i++;
1460            }
1461            
1462            // Delete the corresponding workflow instances and their history
1463            for (Integer workflowId : workflowIds)
1464            {
1465                getJdbcWorkflowStore().clearHistory(workflowId);
1466                getJdbcWorkflowStore().deleteInstance(workflowId);
1467            }
1468            
1469            // Execute the query.
1470            stmt.executeUpdate();
1471        }
1472        catch (SQLException e)
1473        {
1474            __logger.error("Error while deleting entry for table " + tableName, e);
1475            throw new FormsException("Error while deleting entry for table " + tableName, e);
1476        }
1477        finally
1478        {
1479            ConnectionHelper.cleanup(stmt);
1480            ConnectionHelper.cleanup(connection);
1481        }
1482    }
1483    
1484    /**
1485     * Clear all entries of the forms corresponding to the given list of ids.
1486     * @param siteName the name of the site.
1487     * @param formIds the list of form ids
1488     * @throws ProcessingException if an error occurs.
1489     * @throws FormsException if an error occurs.
1490     */
1491    @Callable
1492    public void clearEntries(String siteName, List<String> formIds) throws ProcessingException, FormsException
1493    {
1494        if (StringUtils.isEmpty(siteName) || formIds.isEmpty())
1495        {
1496            throw new ProcessingException("The site name and form ID must be provided.");
1497        }
1498        
1499        for (String formId : formIds)
1500        {
1501            Form form = getFormPropertiesManager().getForm(siteName, formId);
1502            
1503            if (form == null)
1504            {
1505                throw new ProcessingException("The form of ID '" + formId + " can't be found in the site '" + siteName + "'.");
1506            }
1507            
1508            final String tableName = TABLE_PREFIX + form.getId();
1509            
1510            Connection connection = null;
1511            PreparedStatement stmt = null;
1512            
1513            try
1514            {
1515                
1516                String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1517                connection = ConnectionHelper.getConnection(dataSourceId);
1518                String dbType = ConnectionHelper.getDatabaseType(connection);
1519                
1520                String request = "DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName);
1521                
1522                stmt = connection.prepareStatement(request);
1523                
1524                List<Integer> workflowIds = getWorkflowIds(formId, null);
1525                // Delete the corresponding workflow instances and their history
1526                for (Integer workflowId : workflowIds)
1527                {
1528                    getJdbcWorkflowStore().clearHistory(workflowId);
1529                    getJdbcWorkflowStore().deleteInstance(workflowId);
1530                }
1531                
1532                // Execute the query.
1533                stmt.executeUpdate();
1534            }
1535            catch (SQLException e)
1536            {
1537                __logger.error("Error while deleting entry for table " + tableName, e);
1538                throw new FormsException("Error while deleting entry for table " + tableName, e);
1539            }
1540            finally
1541            {
1542                ConnectionHelper.cleanup(stmt);
1543                ConnectionHelper.cleanup(connection);
1544            }
1545        }
1546        
1547    }
1548    
1549    /**
1550     * Get the SELECT statement for retrieving the form entries from the database.
1551     * @param siteName The site name
1552     * @param formId The form id
1553     * @return A result map with the SQL query and the table name
1554     * @throws ProcessingException if the given parameters are wrong
1555     */
1556    @Callable
1557    public Map<String, String> getSelectStatement(String siteName, String formId) throws ProcessingException 
1558    {
1559        Map<String, String> result = new HashMap<>();
1560        
1561        try
1562        {
1563            Form form = getFormPropertiesManager().getForm(siteName, formId);
1564            
1565            if (form == null)
1566            {
1567                throw new ProcessingException("The form of ID '" + formId + " can't be found in the site '" + siteName + "'.");
1568            }
1569            
1570            String tableName = FormTableManager.TABLE_PREFIX + form.getId();
1571
1572            result.put("formId", form.getId());
1573            result.put("tableName", tableName);
1574            
1575            List<String> selectClause = new ArrayList<>();
1576            
1577            selectClause.add("id as Id");
1578            selectClause.add("creationDate as '" + getI18nUtils().translate(new I18nizableText("plugin.forms", "PLUGINS_FORMS_CHOOSE_SHOW_FORM_SUBMISSION_DATE")) + "'");
1579            
1580            Map<String, FieldValue> columns = getColumns(form);
1581            for (FieldValue column : columns.values())
1582            {
1583                selectClause.add(column.getColumnName() + " as '" + column.getField().getLabel() + "'");
1584            }
1585
1586            StringBuilder request = new StringBuilder();
1587            request.append("SELECT ").append(StringUtils.join(selectClause, ", ")).append("\nFROM ").append(tableName).append(";");
1588            
1589            result.put("query", request.toString());
1590        }
1591        catch (FormsException e)
1592        {
1593            __logger.error("An error occurred while getting the results of a form.", e);
1594            throw new ProcessingException("An error occurred while getting the results of a form.", e);
1595        }
1596        
1597        return result;
1598    }
1599    
1600    /**
1601     * Class storing modifications to do on columns.
1602     */
1603    class DbColumnModifications
1604    {
1605        
1606        /** The columns to remove. */
1607        protected Map<String, DbColumn> _columnsToRemove;
1608        
1609        /** The columns to remove. */
1610        protected Map<String, FieldValue> _columnsToAdd;
1611        
1612        /**
1613         * Build a DbColumnModifications object.
1614         */
1615        public DbColumnModifications()
1616        {
1617            this(new HashMap<String, DbColumn>(), new HashMap<String, FieldValue>());
1618        }
1619        
1620        /**
1621         * Build a DbColumnModifications object.
1622         * @param columnsToRemove the columns to remove.
1623         * @param columnsToAdd the columns to add.
1624         */
1625        public DbColumnModifications(Map<String, DbColumn> columnsToRemove, Map<String, FieldValue> columnsToAdd)
1626        {
1627            this._columnsToRemove = columnsToRemove;
1628            this._columnsToAdd = columnsToAdd;
1629        }
1630        
1631        /**
1632         * Get the columnsToRemove.
1633         * @return the columnsToRemove
1634         */
1635        public Map<String, DbColumn> getColumnsToRemove()
1636        {
1637            return _columnsToRemove;
1638        }
1639        
1640        /**
1641         * Set the columnsToRemove.
1642         * @param columnsToRemove the columnsToRemove to set
1643         */
1644        public void setColumnsToRemove(Map<String, DbColumn> columnsToRemove)
1645        {
1646            this._columnsToRemove = columnsToRemove;
1647        }
1648        
1649        /**
1650         * Get the columnsToAdd.
1651         * @return the columnsToAdd
1652         */
1653        public Map<String, FieldValue> getColumnsToAdd()
1654        {
1655            return _columnsToAdd;
1656        }
1657        
1658        /**
1659         * Set the columnsToAdd.
1660         * @param columnsToAdd the columnsToAdd to set
1661         */
1662        public void setColumnsToAdd(Map<String, FieldValue> columnsToAdd)
1663        {
1664            this._columnsToAdd = columnsToAdd;
1665        }
1666        
1667    }
1668
1669    /**
1670     * Class representing a db column.
1671     */
1672    class DbColumn
1673    {
1674        
1675        /** The columns to remove. */
1676        protected String _name;
1677        
1678        /** The columns to remove. */
1679        protected int _sqlType;
1680        
1681        /** The columns to remove. */
1682        protected String _typeName;
1683        
1684        /** The columns to remove. */
1685        protected int _columnSize;
1686        
1687        /**
1688         * Build a DB column object.
1689         */
1690        public DbColumn()
1691        {
1692            this("", 0, "", 0);
1693        }
1694        
1695        /**
1696         * Build a DB column object.
1697         * @param name the column name.
1698         * @param sqlType the sql type.
1699         * @param typeName the type name.
1700         * @param columnSize the column size.
1701         */
1702        public DbColumn(String name, int sqlType, String typeName, int columnSize)
1703        {
1704            this._name = name;
1705            this._sqlType = sqlType;
1706            this._typeName = typeName;
1707            this._columnSize = columnSize;
1708        }
1709        
1710        /**
1711         * Get the name.
1712         * @return the name
1713         */
1714        public String getName()
1715        {
1716            return _name;
1717        }
1718        
1719        /**
1720         * Set the name.
1721         * @param name the name to set
1722         */
1723        public void setName(String name)
1724        {
1725            this._name = name;
1726        }
1727        
1728        /**
1729         * Get the sqlType.
1730         * @return the sqlType
1731         */
1732        public int getSqlType()
1733        {
1734            return _sqlType;
1735        }
1736        
1737        /**
1738         * Set the sqlType.
1739         * @param sqlType the sqlType to set
1740         */
1741        public void setSqlType(int sqlType)
1742        {
1743            this._sqlType = sqlType;
1744        }
1745        
1746        /**
1747         * Get the typeName.
1748         * @return the typeName
1749         */
1750        public String getTypeName()
1751        {
1752            return _typeName;
1753        }
1754        
1755        /**
1756         * Set the typeName.
1757         * @param typeName the typeName to set
1758         */
1759        public void setTypeName(String typeName)
1760        {
1761            this._typeName = typeName;
1762        }
1763        
1764        /**
1765         * Get the columnSize.
1766         * @return the columnSize
1767         */
1768        public int getColumnSize()
1769        {
1770            return _columnSize;
1771        }
1772        
1773        /**
1774         * Set the columnSize.
1775         * @param columnSize the columnSize to set
1776         */
1777        public void setColumnSize(int columnSize)
1778        {
1779            this._columnSize = columnSize;
1780        }
1781        
1782        /**
1783         * Get a type identifier corresponding to the column (i.e. TEXT, VARCHAR(255), INT(1), and so on.)
1784         * @return a type identifier  corresponding to the column (i.e. TEXT, VARCHAR(255), INT(1), and so on.)
1785         */
1786        public String getColumnTypeIdentifier()
1787        {
1788            StringBuilder buff = new StringBuilder();
1789            
1790            buff.append(_typeName);
1791            if (_typeName.equals("VARCHAR") || _typeName.equals("INT") || _typeName.equals("NUMBER"))
1792            {
1793                buff.append('(').append(_columnSize).append(')');
1794            }
1795            
1796            return buff.toString();
1797        }
1798        
1799    }
1800    
1801}