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 COST:
591                case SELECT:
592                    fdValue = new FieldValue(id, Types.VARCHAR, null, field);
593                    columns.put(id, fdValue);
594                    break;
595                case TEXTAREA:
596                    fdValue = new FieldValue(id, Types.LONGVARCHAR, null, field);
597                    columns.put(id, fdValue);
598                    break;
599                case RADIO:
600                    if (!columns.containsKey(name))
601                    {
602                        String value = field.getProperties().get("value");
603                        String label = field.getLabel();
604                        int index = 1;
605                        field.getProperties().put("size", String.valueOf(index));
606                        field.getProperties().put("option-" + index + "-value", value);
607                        field.getProperties().put("option-" + index + "-label", label);
608                        
609                        field.getProperties().remove("value");
610                        
611                        fdValue = new FieldValue(name, Types.VARCHAR, null, field);
612                        columns.put(name, fdValue);
613                    }
614                    else
615                    {
616                        // The value exists, clone it, concatenating the label.
617                        if (StringUtils.isNotEmpty(field.getLabel()))
618                        {
619                            Field radioField = columns.get(name).getField();
620                            int index = Integer.parseInt(radioField.getProperties().get("size"));
621                            index++;
622                            
623                            String value = field.getProperties().get("value");
624                            String label = field.getLabel();
625                            
626                            radioField.getProperties().put("size", String.valueOf(index));
627                            radioField.getProperties().put("option-" + index + "-value", value);
628                            radioField.getProperties().put("option-" + index + "-label", label);
629                            
630                            Field dummyField = new Field(radioField.getId(), radioField.getType(), radioField.getName(), radioField.getLabel() + "/" + field.getLabel(), radioField.getProperties());
631                            columns.get(name).setField(dummyField);
632                        }
633                    }
634                    break;
635                case CHECKBOX:
636                    fdValue = new FieldValue(id, Types.BOOLEAN, null, field);
637                    columns.put(id, fdValue);
638                    break;
639                case FILE:
640                    fdValue = new FieldValue(id, Types.BLOB, null, field);
641                    columns.put(id, fdValue);
642                    break;
643                case CAPTCHA:
644                    break;
645                default:
646                    break;
647            }
648            
649        }
650        
651        return columns;
652    }
653    
654    /**
655     * Get the full column type corresponding to the database type (with precision).
656     * @param sqlType the sqltype value.
657     * @param dbType the database type.
658     * @return the real column type identifier (to be included in CREATE statement).
659     */
660    protected String _getColumnType(int sqlType, String dbType)
661    {
662        switch (sqlType)
663        {
664            case Types.LONGVARCHAR:
665                return  DbTypeHelper.getTextType(dbType);
666            case Types.BOOLEAN:
667                return  DbTypeHelper.getBooleanType(dbType);
668            case Types.BLOB:
669                return  DbTypeHelper.getBinaryType(dbType);
670            case Types.VARCHAR:
671            default:
672                return  DbTypeHelper.getVarcharType(dbType);
673        }
674    }
675    
676    /**
677     * Get the column type name (without precision) corresponding to the SQL type.
678     * @param sqlType the SQL type value.
679     * @param dbType the database type.
680     * @return the real column type name (without precision).
681     */
682    protected String _getColumnTypeName(int sqlType, String dbType)
683    {
684        return StringUtils.substringBefore(_getColumnType(sqlType, dbType), "(");
685    }
686    
687    /**
688     * Test if two columns have the same type.
689     * @param column the column retrieved from the database.
690     * @param newColumn the new column.
691     * @param dbType the database type.
692     * @return true if the two columns have the same type.
693     */
694    protected boolean _isSameType(DbColumn column, FieldValue newColumn, String dbType)
695    {
696        int newColumnType = newColumn.getType();
697        int currentColumnType = column.getSqlType();
698        String newColumnTypeName = _getColumnTypeName(newColumnType, dbType);
699        String currentColumnTypeName = column.getTypeName();
700        
701        return currentColumnType == newColumnType || currentColumnTypeName.equals(newColumnTypeName);
702    }
703    
704    private boolean _createTable(String formId, Map<String, FieldValue> columns)
705    {
706        String tableName = TABLE_PREFIX + formId;
707        
708        Connection connection = null;
709        PreparedStatement stmt = null;
710        
711        try
712        {
713
714            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
715            connection = ConnectionHelper.getConnection(dataSourceId);
716            
717            String dbType = ConnectionHelper.getDatabaseType(connection);
718            StringBuilder sql = new StringBuilder();
719            
720            sql.append("CREATE TABLE ");
721            sql.append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
722            sql.append(" ( ").append(ID_FIELD).append(" ").append(DbTypeHelper.getIdentityType(dbType)).append(" ").append(DbTypeHelper.getIdentityMarker(dbType)).append(", ");
723            sql.append(CREATION_DATE_FIELD).append(" ").append(DbTypeHelper.getDateTimeType(dbType)).append(" NOT NULL,");
724            
725            for (Map.Entry<String, FieldValue> column : columns.entrySet())
726            {
727                int sqlType = column.getValue().getType();
728                sql.append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, column.getKey())).append(" ");
729                sql.append(_getColumnType(sqlType, dbType));
730                sql.append(" DEFAULT NULL,");
731                
732                // Add a column for the file name.
733                if (sqlType == Types.BLOB)
734                {
735                    String fileNameColumn = column.getKey() + FILE_NAME_COLUMN_SUFFIX;
736                    String normalizedName = DbTypeHelper.normalizeName(dbType, fileNameColumn);
737                    
738                    sql.append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, normalizedName)).append(" ");
739                    sql.append(DbTypeHelper.getVarcharType(dbType));
740                    sql.append(" DEFAULT NULL,");
741                }
742            }
743            
744            sql.append("PRIMARY KEY (").append(ID_FIELD).append("))");
745            
746            if (__logger.isDebugEnabled())
747            {
748                __logger.debug("Creating table : " + sql.toString());
749            }
750            
751            stmt = connection.prepareStatement(sql.toString());
752            stmt.executeUpdate();
753            ConnectionHelper.cleanup(stmt);
754            
755            // create sequence for oracle database
756            if (ConnectionHelper.DATABASE_ORACLE.equals(dbType))
757            {
758                String sqlQuery = "create sequence seq_" + formId; 
759                stmt = connection.prepareStatement(sqlQuery);
760                stmt.executeUpdate();
761            }
762            
763            return true;
764        }
765        catch (SQLException e)
766        {
767            __logger.error("Unable to create table " + tableName, e);
768            return false;
769        }
770        finally
771        {
772            ConnectionHelper.cleanup(stmt);
773            ConnectionHelper.cleanup(connection);
774        }
775    }
776    
777    private boolean _alterTable(String formId, Map<String, FieldValue> newColumns)
778    {
779        String tableName = TABLE_PREFIX + formId;
780        
781        Connection connection = null;
782        String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
783        
784        try
785        {
786            connection = ConnectionHelper.getConnection(dataSourceId);
787            
788            // Start transaction.
789            connection.setAutoCommit(false);
790            
791            // Get the existing columns.
792            Map<String, DbColumn> existingColumns = _getExistingColumns(connection, tableName);
793            
794            // Compute the modifications to make.
795            DbColumnModifications modifications = _getColumnsToModify(connection, existingColumns, newColumns);
796            
797            // Move the columns that were deleted or replaced.
798            _moveColumns(connection, modifications.getColumnsToRemove(), existingColumns, tableName);
799            
800            // Add the new columns.
801            _addColumns(connection, modifications.getColumnsToAdd(), tableName);
802            
803            // Commit the transaction.
804            connection.commit();
805        }
806        catch (SQLException e)
807        {
808            __logger.error("Unable to alter table " + tableName, e);
809            try
810            {
811                connection.rollback();
812            }
813            catch (SQLException sqlex)
814            {
815                // Ignore.
816                __logger.error("Error rollbacking the 'alter table' statements for table " + tableName, e);
817            }
818            return false;
819        }
820        finally
821        {
822            ConnectionHelper.cleanup(connection);
823        }
824        return true;
825        
826    }
827    
828    private int _checkTableStatus(String formId, Map<String, FieldValue> newColumns)
829    {
830        String tableName = TABLE_PREFIX + formId;
831        
832        Connection connection = null;
833        ResultSet tables = null;
834        ResultSet rs = null;
835        
836        Map<String, DbColumn> currentColumns = new HashMap<>();
837        
838        try
839        {
840
841            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
842            connection = ConnectionHelper.getConnection(dataSourceId);
843            
844            String dbType = ConnectionHelper.getDatabaseType(connection);
845            
846            tableName = DbTypeHelper.filterName(dbType, tableName);
847            
848            DatabaseMetaData metadata = connection.getMetaData();
849            tables = metadata.getTables(connection.getCatalog(), null, tableName, null);
850            
851            if (!tables.next())
852            {
853                return TABLE_NOT_CREATED;
854            }
855            
856            currentColumns = _getExistingColumns(connection, tableName);
857            
858            for (String newColumn : newColumns.keySet())
859            {
860                String filteredColumn = DbTypeHelper.filterName(dbType, newColumn);
861                
862                // Test the existence of the column.
863                if (!currentColumns.containsKey(filteredColumn))
864                {
865                    return TABLE_CREATED_BUT_NEED_UPDATE;
866                }
867                
868                // Test that the column type has not changed.
869                DbColumn currentColumn = currentColumns.get(filteredColumn);
870                FieldValue newColumnFv = newColumns.get(newColumn);
871                
872                if (!_isSameType(currentColumn, newColumnFv, dbType))
873                {
874                    return TABLE_CREATED_BUT_NEED_UPDATE;
875                }
876            }
877            
878            return TABLE_CREATED_AND_UP_TO_DATE;
879        }
880        catch (SQLException e)
881        {
882            __logger.error("Unable to get columns from table " + tableName, e);
883            return TABLE_UNKOWN_STATUS;
884        }
885        finally
886        {
887            ConnectionHelper.cleanup(tables);
888            ConnectionHelper.cleanup(rs);
889            ConnectionHelper.cleanup(connection);
890        }
891    }
892
893    private boolean _createOrUpdateTable(String formId, final Map<String, FieldValue> columns)
894    {
895        boolean toReturn = true;
896        switch (_checkTableStatus(formId, columns))
897        {
898            case TABLE_NOT_CREATED:
899                if (!_createTable(formId, columns))
900                {
901                    toReturn = false;
902                }
903                break;
904            case TABLE_CREATED_BUT_NEED_UPDATE:
905                if (!_alterTable(formId, columns))
906                {
907                    toReturn = false;
908                }
909                break;
910            case TABLE_CREATED_AND_UP_TO_DATE:
911            case TABLE_UNKOWN_STATUS:
912            default:
913                break;
914        }
915        return toReturn;
916    }
917
918    private DbColumnModifications _getColumnsToModify(Connection con, Map<String, DbColumn> existingColumns, Map<String, FieldValue> newColumns)
919    {
920        DbColumnModifications modifications = new DbColumnModifications();
921        
922        String dbType = ConnectionHelper.getDatabaseType(con);
923        
924        Map<String, FieldValue> columnsToAdd = new LinkedHashMap<>();
925        Map<String, DbColumn> columnsToRemove = new HashMap<>();
926        for (Map.Entry<String, FieldValue> newColumn : newColumns.entrySet())
927        {
928            String filteredName = DbTypeHelper.filterName(dbType, newColumn.getKey());
929            columnsToAdd.put(filteredName, newColumn.getValue());
930        }
931        
932        if (existingColumns != null)
933        {
934            for (String existingColName : existingColumns.keySet())
935            {
936                DbColumn existingColumn = existingColumns.get(existingColName);
937                FieldValue newColumn = columnsToAdd.get(existingColName);
938                
939                // If the column does not already exist or if the type has changed,
940                // mark the current column to be removed and keep the new column in the "to be added" list.
941                if (newColumn != null && !_isSameType(existingColumn, newColumn, dbType))
942                {
943                    columnsToRemove.put(existingColName, existingColumn);
944                }
945                else
946                {
947                    // The column already exists and its type has not changed: do not touch this one.
948                    columnsToAdd.remove(existingColName);
949                }
950            }
951        }
952        
953        modifications.setColumnsToAdd(columnsToAdd);
954        modifications.setColumnsToRemove(columnsToRemove);
955        
956        return modifications;
957    }
958    
959    private Map<String, DbColumn> _getExistingColumns(Connection con, String table) throws SQLException
960    {
961        ResultSet columns = null;
962        Map<String, DbColumn> toReturn = new LinkedHashMap<>();
963        try
964        {
965            String dbType = ConnectionHelper.getDatabaseType(con);
966            String filteredTableName = DbTypeHelper.filterName(dbType, table);
967            
968            DatabaseMetaData metadata = con.getMetaData();
969            columns = metadata.getColumns(con.getCatalog(), null, filteredTableName, null);
970            while (columns.next())
971            {
972                String columnName = columns.getString("COLUMN_NAME");
973                Integer sqlType = columns.getInt("DATA_TYPE");
974                String typeName = columns.getString("TYPE_NAME");
975                Integer colSize = columns.getInt("COLUMN_SIZE");
976                
977                DbColumn col = new DbColumn(columnName, sqlType, typeName, colSize);
978                
979                toReturn.put(columnName, col);
980            }
981        }
982        catch (SQLException e)
983        {
984            __logger.error("Unable to get columns from " + table, e);
985            throw e;
986        }
987        finally
988        {
989            ConnectionHelper.cleanup(columns);
990        }
991        return toReturn;
992    }
993    
994    private void _moveColumns(Connection con, Map<String, DbColumn> columnsToRemove, Map<String, DbColumn> existingColumns, String tableName) throws SQLException
995    {
996        String dbType = ConnectionHelper.getDatabaseType(con);
997        PreparedStatement stmt = null;
998        
999        Set<String> existingColumnNames = existingColumns.keySet();
1000        
1001        try
1002        {
1003            for (String columnName : columnsToRemove.keySet())
1004            {
1005                DbColumn columnToRemove = columnsToRemove.get(columnName);
1006                
1007                String newName = _getNewName(con, columnName, existingColumnNames);
1008                
1009                String sql = DbTypeHelper.getRenameColumnStatement(tableName, columnToRemove, newName, dbType, getSQLDatabaseTypeExtensionPoint());
1010                
1011                if (__logger.isDebugEnabled())
1012                {
1013                    __logger.debug("Moving column: " + sql);
1014                }
1015                
1016                stmt = con.prepareStatement(sql);
1017                stmt.executeUpdate();
1018                
1019                // Add a column for the file name.
1020                if (columnToRemove.getSqlType() == Types.BLOB)
1021                {
1022                    // Release the previous statement.
1023                    ConnectionHelper.cleanup(stmt);
1024                    
1025                    String fileNameColumn = columnName + FILE_NAME_COLUMN_SUFFIX;
1026                    String newFileNameColumn = _getNewName(con, fileNameColumn, existingColumnNames);
1027                    String varcharType = DbTypeHelper.getVarcharType(dbType);
1028                    
1029                    sql = DbTypeHelper.getRenameColumnStatement(tableName, fileNameColumn, newFileNameColumn, varcharType, dbType, getSQLDatabaseTypeExtensionPoint());
1030                    
1031                    if (__logger.isDebugEnabled())
1032                    {
1033                        __logger.debug("Altering table : " + sql.toString());
1034                    }
1035                    
1036                    stmt = con.prepareStatement(sql.toString());
1037                    stmt.executeUpdate();
1038                }
1039            }
1040        }
1041        catch (SQLException e)
1042        {
1043            ConnectionHelper.cleanup(stmt);
1044            throw e;
1045        }
1046    }
1047    
1048    private void _addColumns(Connection con, Map<String, FieldValue> columnsToAdd, String tableName) throws SQLException
1049    {
1050        String dbType = ConnectionHelper.getDatabaseType(con);
1051        PreparedStatement stmt = null;
1052        
1053        try
1054        {
1055            for (Entry<String, FieldValue> column : columnsToAdd.entrySet())
1056            {
1057                StringBuilder sql = new StringBuilder();
1058                
1059                sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1060                
1061                int sqlType = column.getValue().getType();
1062                
1063                sql.append(" ADD ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, column.getKey())).append(" ");
1064                sql.append(_getColumnType(sqlType, dbType));
1065                sql.append(" DEFAULT NULL");
1066                
1067                if (__logger.isDebugEnabled())
1068                {
1069                    __logger.debug("Altering table : " + sql.toString());
1070                }
1071                
1072                stmt = con.prepareStatement(sql.toString());
1073                stmt.executeUpdate();
1074                
1075                // Add a column for the file name.
1076                if (sqlType == Types.BLOB)
1077                {
1078                    // Release the previous statement.
1079                    ConnectionHelper.cleanup(stmt);
1080                    
1081                    sql.setLength(0);
1082                    sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1083                    
1084                    String fileNameColumn = column.getKey() + FILE_NAME_COLUMN_SUFFIX;
1085                    String normalizedName = DbTypeHelper.normalizeName(dbType, fileNameColumn);
1086                    
1087                    sql.append(" ADD ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, normalizedName)).append(" ");
1088                    sql.append(DbTypeHelper.getVarcharType(dbType));
1089                    sql.append(" DEFAULT NULL");
1090                    
1091                    if (__logger.isDebugEnabled())
1092                    {
1093                        __logger.debug("Adding column: " + sql.toString());
1094                    }
1095                    
1096                    stmt = con.prepareStatement(sql.toString());
1097                    stmt.executeUpdate();
1098                }
1099            }
1100        }
1101        catch (SQLException e)
1102        {
1103            ConnectionHelper.cleanup(stmt);
1104            throw e;
1105        }
1106    }
1107    
1108    /**
1109     * Get the new name of a column to be moved.
1110     * @param con the connection.
1111     * @param currentName the current name.
1112     * @param existingColumnNames the names of the existing columns
1113     * @return the new name.
1114     */
1115    private String _getNewName(Connection con, String currentName, Set<String> existingColumnNames)
1116    {
1117        String dbType = ConnectionHelper.getDatabaseType(con);
1118        
1119        int i = 1;
1120        String newName = DbTypeHelper.normalizeName(dbType, currentName + "_old" + i);
1121        String filteredNewName = DbTypeHelper.filterName(dbType, newName);
1122        
1123        while (existingColumnNames.contains(filteredNewName))
1124        {
1125            i++;
1126            newName = DbTypeHelper.normalizeName(dbType, currentName + "_old" + i);
1127            filteredNewName = DbTypeHelper.filterName(dbType, newName);
1128        }
1129        
1130        return newName;
1131    }
1132    
1133    /**
1134     * Get the SQL type corresponding to a field type.
1135     * @param fieldType the field
1136     * @return the sql type as indicated in {@link java.sql.Types}.
1137     */
1138    public static int getFieldSqlType(FieldType fieldType)
1139    {
1140        int sqlType = Types.VARCHAR;
1141        switch (fieldType)
1142        {
1143            case TEXT:
1144            case HIDDEN:
1145            case PASSWORD:
1146            case SELECT:
1147            case RADIO:
1148                sqlType = Types.VARCHAR;
1149                break;
1150            case TEXTAREA:
1151                sqlType = Types.LONGVARCHAR;
1152                break;
1153            case CHECKBOX:
1154                sqlType = Types.BOOLEAN;
1155                break;
1156            case FILE:
1157                sqlType = Types.BLOB;
1158                break;
1159            case CAPTCHA:
1160                sqlType = Types.OTHER;
1161                break;
1162            default:
1163                break;
1164        }
1165        return sqlType;
1166    }
1167    
1168    /**
1169     * Add a workflow id column to the given table
1170     * @param formId the id of the form to alter
1171     * @throws FormsException if an error occurs
1172     */
1173    public void addWorkflowIdColumn(String formId) throws FormsException
1174    {
1175        final String tableName = TABLE_PREFIX + formId;
1176        
1177        Connection con = null;
1178        PreparedStatement stmt = null;
1179        
1180        try
1181        {
1182            // Connect to the database.
1183            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1184            con = ConnectionHelper.getConnection(dataSourceId);
1185            String dbType = ConnectionHelper.getDatabaseType(con);
1186            StringBuilder sql = new StringBuilder();
1187
1188            sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1189            sql.append(" ADD ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, WORKFLOW_ID_FIELD)).append(" ");
1190            sql.append(DbTypeHelper.getIntegerType(dbType));
1191
1192            if (__logger.isDebugEnabled())
1193            {
1194                __logger.debug("Adding column: " + sql.toString());
1195            }
1196
1197            stmt = con.prepareStatement(sql.toString());
1198            stmt.executeUpdate();
1199        }
1200        catch (SQLException e)
1201        {
1202            __logger.error("Error while adding the workflow id column for the table " + tableName, e);
1203            throw new FormsException("Error while adding a column to the table " + tableName, e);
1204        }
1205        finally
1206        {
1207            // Clean up connection resources
1208            ConnectionHelper.cleanup(stmt);
1209            ConnectionHelper.cleanup(con);
1210        }
1211    }
1212    
1213    /**
1214     * Delete the workflow id column of the given table
1215     * @param formId the id of the form to alter
1216     * @throws FormsException if an error occurs
1217     */
1218    public void dropWorkflowIdColumn(String formId) throws FormsException
1219    {
1220        final String tableName = TABLE_PREFIX + formId;
1221        
1222        Connection con = null;
1223        PreparedStatement stmt = null;
1224        
1225        try
1226        {
1227            // Connect to the database.
1228            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1229            con = ConnectionHelper.getConnection(dataSourceId);
1230            String dbType = ConnectionHelper.getDatabaseType(con);
1231            StringBuilder sql = new StringBuilder();
1232
1233            sql.append("ALTER TABLE ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName));
1234            sql.append(" DROP COLUMN ").append(getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, WORKFLOW_ID_FIELD));
1235
1236            if (__logger.isDebugEnabled())
1237            {
1238                __logger.debug("Deleting column: " + sql.toString());
1239            }
1240
1241            stmt = con.prepareStatement(sql.toString());
1242            stmt.executeUpdate();
1243        }
1244        catch (SQLException e)
1245        {
1246            __logger.error("Error while deleting the workflow id column for the table " + tableName, e);
1247            throw new FormsException("Error while deleting a column from the table " + tableName, e);
1248        }
1249        finally
1250        {
1251            // Clean up connection resources
1252            ConnectionHelper.cleanup(stmt);
1253            ConnectionHelper.cleanup(con);
1254        }
1255    }
1256
1257    /**
1258     * Set a new workflow id to the column that has the given old workflow id
1259     * @param form the form
1260     * @param entryId the id of the entry
1261     * @param newWorkflowId the new workflow id
1262     * @throws FormsException if an error occurs
1263     */
1264    public void setWorkflowId(Form form, long entryId, long newWorkflowId) throws FormsException
1265    {
1266        final String tableName = TABLE_PREFIX + form.getId();
1267
1268        Connection con = null;
1269        PreparedStatement stmt = null;
1270        
1271        try
1272        {
1273            // Connect to the database.
1274            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1275            con = ConnectionHelper.getConnection(dataSourceId);
1276            String dbType = ConnectionHelper.getDatabaseType(con);
1277            
1278            String query = "UPDATE " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName);
1279            query += " SET "  + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, WORKFLOW_ID_FIELD) + " = ?";
1280            query += " WHERE " + ID_FIELD + " = ?";
1281
1282            stmt = con.prepareStatement(query);
1283
1284            stmt.setLong(1, newWorkflowId);
1285            stmt.setLong(2, entryId);
1286
1287            // Execute the query.
1288            stmt.executeUpdate();
1289        }
1290        catch (SQLException e)
1291        {
1292            __logger.error("Error while resetting the workflow id for the table " + tableName, e);
1293            throw new FormsException("Error while deleting entry for table " + tableName, e);
1294        }
1295        finally
1296        {
1297            // Clean up connection resources
1298            ConnectionHelper.cleanup(stmt);
1299            ConnectionHelper.cleanup(con);
1300        }
1301    }
1302    
1303    /**
1304     * Get the workflow id of the given form entry or of all the entries
1305     * @param formId the id of the form
1306     * @param entryId the entry to get the workflow id from. If null, all the workflow ids are returned
1307     * @throws FormsException if an error occurs.
1308     * @return the list of workflow ids
1309     */
1310    public List<Integer> getWorkflowIds(String formId, Integer entryId) throws FormsException
1311    {
1312        List<Integer> workflowIds = new ArrayList<> ();
1313
1314        final String tableName = TABLE_PREFIX + formId;
1315        
1316        Connection con = null;
1317        PreparedStatement stmt = null;
1318        ResultSet rs = null;
1319        
1320        try
1321        {
1322            if (!hasWorkflowIdColumn(formId))
1323            {
1324                return Collections.EMPTY_LIST;
1325            }
1326            
1327            // Connect to the database.
1328            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1329            con = ConnectionHelper.getConnection(dataSourceId);
1330            String dbType = ConnectionHelper.getDatabaseType(con);
1331            
1332            String query = "SELECT " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, "workflowId") + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName);
1333            if (entryId != null)
1334            {
1335                query += " WHERE " + ID_FIELD + " = ?";
1336            }
1337
1338            stmt = con.prepareStatement(query);
1339
1340            if (entryId != null)
1341            {
1342                stmt.setInt(1, entryId);
1343            }
1344            
1345            // Execute the query.
1346            rs = stmt.executeQuery();
1347
1348            // Extract the result(s).
1349            while (rs.next())
1350            {
1351                workflowIds.add(rs.getInt(1));
1352            }
1353        }
1354        catch (SQLException e)
1355        {
1356            __logger.error("Error while getting workflow ids from the table " + tableName, e);
1357            throw new FormsException("Error while getting workflow ids from the table " + tableName, e);
1358        }
1359        finally
1360        {
1361            // Clean up connection resources
1362            ConnectionHelper.cleanup(rs);
1363            ConnectionHelper.cleanup(stmt);
1364            ConnectionHelper.cleanup(con);
1365        }
1366
1367        return workflowIds;
1368    }
1369    
1370    /**
1371     * Does the given form have a workflow id column in its SQL table ?
1372     * @param formId the id of the form
1373     * @return true if the form has a workflow id column, false otherwise
1374     * @throws SQLException if a database access error occurs
1375     */
1376    public boolean hasWorkflowIdColumn (String formId) throws SQLException
1377    {
1378        boolean hasWorkflowId = true;
1379        
1380        Connection con = null;
1381        final String tableName = TABLE_PREFIX + formId;
1382        
1383        try
1384        {
1385            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1386            con = ConnectionHelper.getConnection(dataSourceId);
1387            Map<String, DbColumn> currentColumns = _getExistingColumns(con, tableName);        
1388            
1389            // Check if the column contains a workflow id
1390            if (!currentColumns.containsKey(WORKFLOW_ID_FIELD))
1391            {
1392                hasWorkflowId = false;
1393            }
1394            
1395            return hasWorkflowId;
1396        }
1397        finally 
1398        {
1399            ConnectionHelper.cleanup(con);
1400        }
1401    }
1402    
1403    /**
1404     * Delete a list of entries of a form
1405     * @param siteName The site name
1406     * @param formId The form id
1407     * @param entries The list of entries to delete
1408     * @throws ProcessingException if the given parameters are wrong
1409     * @throws FormsException if an error occurs when deleting the form submission
1410     */
1411    @Callable
1412    public void deleteEntry(String siteName, String formId, List<Integer> entries) throws ProcessingException, FormsException
1413    {
1414        if (StringUtils.isEmpty(siteName) || StringUtils.isEmpty(formId))
1415        {
1416            throw new ProcessingException("The site name and form ID must be provided.");
1417        }
1418    
1419        Form form = getFormPropertiesManager().getForm(siteName, formId);
1420        
1421        if (form == null)
1422        {
1423            throw new ProcessingException("The form of ID '" + formId + " can't be found in the site '" + siteName + "'.");
1424        }
1425        
1426        final String tableName = TABLE_PREFIX + form.getId();
1427        
1428        Connection connection = null;
1429        PreparedStatement stmt = null;
1430        
1431        try
1432        {
1433            String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1434            connection = ConnectionHelper.getConnection(dataSourceId);
1435            String dbType = ConnectionHelper.getDatabaseType(connection);
1436            
1437            StringBuilder sb = new StringBuilder();
1438            sb.append("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName) + " WHERE ");
1439
1440            
1441            int i;
1442            int count = entries.size();
1443            for (i = 0; i < count; i++)
1444            {
1445                if (i > 0)
1446                {
1447                    sb.append(" OR ");
1448                }
1449                sb.append(ID_FIELD).append("=?");
1450            }
1451            
1452            stmt = connection.prepareStatement(sb.toString());
1453            
1454            i = 1;
1455            List<Integer> workflowIds = new ArrayList<> ();
1456            for (Integer entryId : entries)
1457            {
1458                workflowIds.addAll(getWorkflowIds(formId, entryId));
1459                stmt.setInt(i, entryId);
1460                i++;
1461            }
1462            
1463            // Delete the corresponding workflow instances and their history
1464            for (Integer workflowId : workflowIds)
1465            {
1466                getJdbcWorkflowStore().clearHistory(workflowId);
1467                getJdbcWorkflowStore().deleteInstance(workflowId);
1468            }
1469            
1470            // Execute the query.
1471            stmt.executeUpdate();
1472        }
1473        catch (SQLException e)
1474        {
1475            __logger.error("Error while deleting entry for table " + tableName, e);
1476            throw new FormsException("Error while deleting entry for table " + tableName, e);
1477        }
1478        finally
1479        {
1480            ConnectionHelper.cleanup(stmt);
1481            ConnectionHelper.cleanup(connection);
1482        }
1483    }
1484    
1485    /**
1486     * Clear all entries of the forms corresponding to the given list of ids.
1487     * @param siteName the name of the site.
1488     * @param formIds the list of form ids
1489     * @throws ProcessingException if an error occurs.
1490     * @throws FormsException if an error occurs.
1491     */
1492    @Callable
1493    public void clearEntries(String siteName, List<String> formIds) throws ProcessingException, FormsException
1494    {
1495        if (StringUtils.isEmpty(siteName) || formIds.isEmpty())
1496        {
1497            throw new ProcessingException("The site name and form ID must be provided.");
1498        }
1499        
1500        for (String formId : formIds)
1501        {
1502            Form form = getFormPropertiesManager().getForm(siteName, formId);
1503            
1504            if (form == null)
1505            {
1506                throw new ProcessingException("The form of ID '" + formId + " can't be found in the site '" + siteName + "'.");
1507            }
1508            
1509            final String tableName = TABLE_PREFIX + form.getId();
1510            
1511            Connection connection = null;
1512            PreparedStatement stmt = null;
1513            
1514            try
1515            {
1516                
1517                String dataSourceId = Config.getInstance().getValue(FORMS_POOL_CONFIG_PARAM);
1518                connection = ConnectionHelper.getConnection(dataSourceId);
1519                String dbType = ConnectionHelper.getDatabaseType(connection);
1520                
1521                String request = "DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, tableName);
1522                
1523                stmt = connection.prepareStatement(request);
1524                
1525                List<Integer> workflowIds = getWorkflowIds(formId, null);
1526                // Delete the corresponding workflow instances and their history
1527                for (Integer workflowId : workflowIds)
1528                {
1529                    getJdbcWorkflowStore().clearHistory(workflowId);
1530                    getJdbcWorkflowStore().deleteInstance(workflowId);
1531                }
1532                
1533                // Execute the query.
1534                stmt.executeUpdate();
1535            }
1536            catch (SQLException e)
1537            {
1538                __logger.error("Error while deleting entry for table " + tableName, e);
1539                throw new FormsException("Error while deleting entry for table " + tableName, e);
1540            }
1541            finally
1542            {
1543                ConnectionHelper.cleanup(stmt);
1544                ConnectionHelper.cleanup(connection);
1545            }
1546        }
1547        
1548    }
1549    
1550    /**
1551     * Get the SELECT statement for retrieving the form entries from the database.
1552     * @param siteName The site name
1553     * @param formId The form id
1554     * @return A result map with the SQL query and the table name
1555     * @throws ProcessingException if the given parameters are wrong
1556     */
1557    @Callable
1558    public Map<String, String> getSelectStatement(String siteName, String formId) throws ProcessingException 
1559    {
1560        Map<String, String> result = new HashMap<>();
1561        
1562        try
1563        {
1564            Form form = getFormPropertiesManager().getForm(siteName, formId);
1565            
1566            if (form == null)
1567            {
1568                throw new ProcessingException("The form of ID '" + formId + " can't be found in the site '" + siteName + "'.");
1569            }
1570            
1571            String tableName = FormTableManager.TABLE_PREFIX + form.getId();
1572
1573            result.put("formId", form.getId());
1574            result.put("tableName", tableName);
1575            
1576            List<String> selectClause = new ArrayList<>();
1577            
1578            selectClause.add("id as Id");
1579            selectClause.add("creationDate as '" + getI18nUtils().translate(new I18nizableText("plugin.forms", "PLUGINS_FORMS_CHOOSE_SHOW_FORM_SUBMISSION_DATE")) + "'");
1580            
1581            Map<String, FieldValue> columns = getColumns(form);
1582            for (FieldValue column : columns.values())
1583            {
1584                selectClause.add(column.getColumnName() + " as '" + column.getField().getLabel() + "'");
1585            }
1586
1587            StringBuilder request = new StringBuilder();
1588            request.append("SELECT ").append(StringUtils.join(selectClause, ", ")).append("\nFROM ").append(tableName).append(";");
1589            
1590            result.put("query", request.toString());
1591        }
1592        catch (FormsException e)
1593        {
1594            __logger.error("An error occurred while getting the results of a form.", e);
1595            throw new ProcessingException("An error occurred while getting the results of a form.", e);
1596        }
1597        
1598        return result;
1599    }
1600    
1601    /**
1602     * Class storing modifications to do on columns.
1603     */
1604    class DbColumnModifications
1605    {
1606        
1607        /** The columns to remove. */
1608        protected Map<String, DbColumn> _columnsToRemove;
1609        
1610        /** The columns to remove. */
1611        protected Map<String, FieldValue> _columnsToAdd;
1612        
1613        /**
1614         * Build a DbColumnModifications object.
1615         */
1616        public DbColumnModifications()
1617        {
1618            this(new HashMap<String, DbColumn>(), new HashMap<String, FieldValue>());
1619        }
1620        
1621        /**
1622         * Build a DbColumnModifications object.
1623         * @param columnsToRemove the columns to remove.
1624         * @param columnsToAdd the columns to add.
1625         */
1626        public DbColumnModifications(Map<String, DbColumn> columnsToRemove, Map<String, FieldValue> columnsToAdd)
1627        {
1628            this._columnsToRemove = columnsToRemove;
1629            this._columnsToAdd = columnsToAdd;
1630        }
1631        
1632        /**
1633         * Get the columnsToRemove.
1634         * @return the columnsToRemove
1635         */
1636        public Map<String, DbColumn> getColumnsToRemove()
1637        {
1638            return _columnsToRemove;
1639        }
1640        
1641        /**
1642         * Set the columnsToRemove.
1643         * @param columnsToRemove the columnsToRemove to set
1644         */
1645        public void setColumnsToRemove(Map<String, DbColumn> columnsToRemove)
1646        {
1647            this._columnsToRemove = columnsToRemove;
1648        }
1649        
1650        /**
1651         * Get the columnsToAdd.
1652         * @return the columnsToAdd
1653         */
1654        public Map<String, FieldValue> getColumnsToAdd()
1655        {
1656            return _columnsToAdd;
1657        }
1658        
1659        /**
1660         * Set the columnsToAdd.
1661         * @param columnsToAdd the columnsToAdd to set
1662         */
1663        public void setColumnsToAdd(Map<String, FieldValue> columnsToAdd)
1664        {
1665            this._columnsToAdd = columnsToAdd;
1666        }
1667        
1668    }
1669
1670    /**
1671     * Class representing a db column.
1672     */
1673    class DbColumn
1674    {
1675        
1676        /** The columns to remove. */
1677        protected String _name;
1678        
1679        /** The columns to remove. */
1680        protected int _sqlType;
1681        
1682        /** The columns to remove. */
1683        protected String _typeName;
1684        
1685        /** The columns to remove. */
1686        protected int _columnSize;
1687        
1688        /**
1689         * Build a DB column object.
1690         */
1691        public DbColumn()
1692        {
1693            this("", 0, "", 0);
1694        }
1695        
1696        /**
1697         * Build a DB column object.
1698         * @param name the column name.
1699         * @param sqlType the sql type.
1700         * @param typeName the type name.
1701         * @param columnSize the column size.
1702         */
1703        public DbColumn(String name, int sqlType, String typeName, int columnSize)
1704        {
1705            this._name = name;
1706            this._sqlType = sqlType;
1707            this._typeName = typeName;
1708            this._columnSize = columnSize;
1709        }
1710        
1711        /**
1712         * Get the name.
1713         * @return the name
1714         */
1715        public String getName()
1716        {
1717            return _name;
1718        }
1719        
1720        /**
1721         * Set the name.
1722         * @param name the name to set
1723         */
1724        public void setName(String name)
1725        {
1726            this._name = name;
1727        }
1728        
1729        /**
1730         * Get the sqlType.
1731         * @return the sqlType
1732         */
1733        public int getSqlType()
1734        {
1735            return _sqlType;
1736        }
1737        
1738        /**
1739         * Set the sqlType.
1740         * @param sqlType the sqlType to set
1741         */
1742        public void setSqlType(int sqlType)
1743        {
1744            this._sqlType = sqlType;
1745        }
1746        
1747        /**
1748         * Get the typeName.
1749         * @return the typeName
1750         */
1751        public String getTypeName()
1752        {
1753            return _typeName;
1754        }
1755        
1756        /**
1757         * Set the typeName.
1758         * @param typeName the typeName to set
1759         */
1760        public void setTypeName(String typeName)
1761        {
1762            this._typeName = typeName;
1763        }
1764        
1765        /**
1766         * Get the columnSize.
1767         * @return the columnSize
1768         */
1769        public int getColumnSize()
1770        {
1771            return _columnSize;
1772        }
1773        
1774        /**
1775         * Set the columnSize.
1776         * @param columnSize the columnSize to set
1777         */
1778        public void setColumnSize(int columnSize)
1779        {
1780            this._columnSize = columnSize;
1781        }
1782        
1783        /**
1784         * Get a type identifier corresponding to the column (i.e. TEXT, VARCHAR(255), INT(1), and so on.)
1785         * @return a type identifier  corresponding to the column (i.e. TEXT, VARCHAR(255), INT(1), and so on.)
1786         */
1787        public String getColumnTypeIdentifier()
1788        {
1789            StringBuilder buff = new StringBuilder();
1790            
1791            buff.append(_typeName);
1792            if (_typeName.equals("VARCHAR") || _typeName.equals("INT") || _typeName.equals("NUMBER"))
1793            {
1794                buff.append('(').append(_columnSize).append(')');
1795            }
1796            
1797            return buff.toString();
1798        }
1799        
1800    }
1801    
1802}