001/*
002 *  Copyright 2018 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.contentio.export.sql;
017
018import java.io.IOException;
019import java.io.InputStream;
020import java.sql.Connection;
021import java.sql.PreparedStatement;
022import java.sql.SQLException;
023import java.sql.Timestamp;
024import java.sql.Types;
025import java.time.LocalDate;
026import java.time.ZonedDateTime;
027import java.util.ArrayList;
028import java.util.Arrays;
029import java.util.LinkedList;
030import java.util.List;
031import java.util.Locale;
032import java.util.Map;
033import java.util.Map.Entry;
034import java.util.Set;
035
036import org.apache.avalon.framework.component.Component;
037import org.apache.avalon.framework.service.ServiceException;
038import org.apache.avalon.framework.service.ServiceManager;
039import org.apache.avalon.framework.service.Serviceable;
040import org.apache.commons.io.IOUtils;
041import org.apache.commons.io.input.AutoCloseInputStream;
042import org.apache.commons.lang.StringUtils;
043
044import org.ametys.cms.CmsConstants;
045import org.ametys.cms.contenttype.ContentType;
046import org.ametys.cms.contenttype.ContentTypeExtensionPoint;
047import org.ametys.cms.data.ContentValue;
048import org.ametys.cms.data.File;
049import org.ametys.cms.data.Geocode;
050import org.ametys.cms.data.NamedResource;
051import org.ametys.cms.data.RichText;
052import org.ametys.cms.data.type.ModelItemTypeConstants;
053import org.ametys.cms.languages.LanguagesManager;
054import org.ametys.cms.repository.Content;
055import org.ametys.cms.repository.ContentQueryHelper;
056import org.ametys.cms.repository.ContentTypeExpression;
057import org.ametys.core.datasource.ConnectionHelper;
058import org.ametys.core.user.User;
059import org.ametys.core.user.UserIdentity;
060import org.ametys.core.user.UserManager;
061import org.ametys.core.util.I18nUtils;
062import org.ametys.plugins.repository.AmetysObjectIterable;
063import org.ametys.plugins.repository.AmetysObjectResolver;
064import org.ametys.plugins.repository.AmetysRepositoryException;
065import org.ametys.plugins.repository.data.holder.ModelAwareDataHolder;
066import org.ametys.plugins.repository.data.holder.group.ModelAwareComposite;
067import org.ametys.plugins.repository.data.holder.group.ModelAwareRepeater;
068import org.ametys.plugins.repository.data.holder.group.ModelAwareRepeaterEntry;
069import org.ametys.plugins.repository.metadata.MultilingualString;
070import org.ametys.plugins.repository.model.CompositeDefinition;
071import org.ametys.plugins.repository.model.RepeaterDefinition;
072import org.ametys.plugins.repository.query.expression.Expression.Operator;
073import org.ametys.plugins.repository.version.VersionableAmetysObject;
074import org.ametys.runtime.config.Config;
075import org.ametys.runtime.i18n.I18nizableText;
076import org.ametys.runtime.model.ElementDefinition;
077import org.ametys.runtime.model.ModelItem;
078import org.ametys.runtime.model.ModelItemContainer;
079import org.ametys.runtime.plugin.component.AbstractLogEnabled;
080
081/**
082 * Fill sql table component
083 */
084public class FillSqlTableComponent extends AbstractLogEnabled implements Component, Serviceable
085{
086    /** The component role */
087    public static final String ROLE = FillSqlTableComponent.class.getName();
088    
089    private static final String _EXCLUDE_XML_TAGS = "<(.*?)>";
090    
091    /** Content type extension point. */
092    protected ContentTypeExtensionPoint _contentTypeExtensionPoint;
093  
094    /** The ametys object resolver. */
095    protected AmetysObjectResolver _resolver;
096  
097    /** The normalise name component. */
098    protected NormalizeNameComponent _normalizeNameComponent;
099    
100    /** The configured list of content types to export associated to the SQL table name to use */
101    protected Map<String, String> _contentTypesToExport;
102    
103    /** The i18n translator. */
104    protected I18nUtils _i18nTranslator;
105    
106    /** The language manager */
107    protected LanguagesManager _languageManager;
108    
109    /** The user manager */
110    protected UserManager _userManager;
111
112    private Connection _connection;
113    private String _sqlTablePrefix;
114    private String _sqlPrefixConf;
115    private Map<String, ExportTableInfo> _tablesInfos;
116    private String _mappingPolicy;
117    private LinkedList<PreparedStatement> _stmtList;
118    private boolean _exportOnlyValidatedContent;
119    private boolean _exportNoMultiValuedTable;
120    private String _separator;
121    
122    public void service(ServiceManager manager) throws ServiceException
123    {
124        _contentTypeExtensionPoint = (ContentTypeExtensionPoint) manager.lookup(ContentTypeExtensionPoint.ROLE);
125        _resolver = (AmetysObjectResolver) manager.lookup(AmetysObjectResolver.ROLE);
126        _normalizeNameComponent = (NormalizeNameComponent) manager.lookup(NormalizeNameComponent.ROLE);
127        _i18nTranslator = (I18nUtils) manager.lookup(I18nUtils.ROLE);
128        _languageManager = (LanguagesManager) manager.lookup(LanguagesManager.ROLE);
129        _userManager = (UserManager) manager.lookup(UserManager.ROLE);
130    }
131    
132    /**
133     * Fill table with contents
134     * @param exportConfiguration the content export configuration
135     * @param tableInfo the map of table information
136     * @throws SQLException if a sql error occurred
137     * @throws AmetysRepositoryException if an ametys repository error occurred
138     * @throws IOException if an IO error occurred
139     */
140    public synchronized void fillTable(ExportConfiguration exportConfiguration, Map<String, ExportTableInfo> tableInfo) throws SQLException, AmetysRepositoryException, IOException
141    {
142        // Get from configuration
143        _sqlTablePrefix = exportConfiguration.getTablePrefix();
144        _sqlPrefixConf = exportConfiguration.getTablePrefix();
145        _mappingPolicy = exportConfiguration.getMappingPolicy();
146        _contentTypesToExport = exportConfiguration.getContentTypesToExport();
147        _exportOnlyValidatedContent = exportConfiguration.exportOnlyValidatedContent();
148        _exportNoMultiValuedTable = exportConfiguration.exportNoMultiValuedTable();
149        _separator = exportConfiguration.getSeparator();
150
151        // Initialization
152        _tablesInfos = tableInfo;
153        _stmtList = new LinkedList<>();
154        
155        try
156        {
157            String datasourceId = Config.getInstance().getValue("org.ametys.plugins.contentio.content.export.datasource");
158            _connection = ConnectionHelper.getConnection(datasourceId); 
159            insertValues();
160        }
161        finally
162        {
163            ConnectionHelper.cleanup(_connection);
164            _sqlTablePrefix = null;
165            _sqlPrefixConf = null;
166            _mappingPolicy = null;
167            _contentTypesToExport = null;
168            _separator = null;
169            _stmtList = null;
170        }
171    }
172    
173    private void executeInsert() throws SQLException
174    {
175        for (PreparedStatement stmt : _stmtList)
176        {
177            try
178            {
179                if (getLogger().isDebugEnabled())
180                {
181                    getLogger().debug("Query : {}", stmt.toString());
182                }
183                stmt.executeBatch();
184            }
185            catch (SQLException e)
186            {
187                throw new SQLException(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_ERROR_SQL")), e);
188            }
189            finally
190            {
191                ConnectionHelper.cleanup(stmt);
192            }
193        }
194        
195        _stmtList.clear();
196    }
197
198    /**
199     * Fill the SQL tables with values from JCR
200     * @throws SQLException if a sql error occurred
201     * @throws AmetysRepositoryException if an ametys repository error occurred
202     * @throws IOException if an IO error occurred
203     */
204    protected void insertValues() throws SQLException, AmetysRepositoryException, IOException
205    {
206        boolean isInfoEnabled = getLogger().isInfoEnabled();
207        
208        int nbTotalTypeContenu = _contentTypesToExport.entrySet().size();
209        if (isInfoEnabled && nbTotalTypeContenu != 0)
210        {
211            List<String> i18nParams = new ArrayList<>();
212            i18nParams.add(String.valueOf(nbTotalTypeContenu));
213            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_ANALYZE_BEGIN", i18nParams)));
214        }
215        
216        int nbTypeContenu = 0;
217        int pourcentMax = 10;
218        for (Entry<String, String> entry : _contentTypesToExport.entrySet()) 
219        {
220            String contentTypeId = entry.getKey();
221            ContentType contentType = _contentTypeExtensionPoint.getExtension(contentTypeId);
222            
223            if (!contentType.isAbstract())
224            {
225                _sqlTablePrefix = entry.getValue();
226                
227                if (isInfoEnabled)
228                {
229                    List<String> i18nParams = new ArrayList<>();
230                    i18nParams.add(String.valueOf(entry.getValue()));
231                    getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_CONTENT_TYPE", i18nParams)));
232                }
233                
234                fillTableForContentType(contentTypeId);
235            }
236            
237            int pourcent = nbTypeContenu * 100 / nbTotalTypeContenu;
238            if (pourcent >= pourcentMax)
239            {
240                if (isInfoEnabled)
241                {
242                    List<String> i18nParams = new ArrayList<>();
243                    i18nParams.add(String.valueOf(pourcentMax));
244                    getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_ANALYZE", i18nParams)));
245                }
246                pourcentMax += 10;
247            }
248            
249            nbTypeContenu++;
250            _sqlTablePrefix = _sqlPrefixConf;
251        }
252        
253        if (isInfoEnabled)
254        {
255            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_END")));
256        }
257    }
258    
259    /**
260     * Fill values from JCR request with specific content type
261     * @param contentTypeId the content type id
262     * @throws SQLException if a sql error occurred
263     * @throws AmetysRepositoryException if an ametys repository error occurred
264     * @throws IOException if an IO error occurred
265     */
266    protected void fillTableForContentType(String contentTypeId) throws SQLException, AmetysRepositoryException, IOException
267    {
268        String tableName = getTableName(contentTypeId);
269        
270        ContentTypeExpression typeE = new ContentTypeExpression(Operator.EQ, contentTypeId);
271        String xpath = ContentQueryHelper.getContentXPathQuery(typeE);
272        AmetysObjectIterable<Content> contents = _resolver.query(xpath);
273        
274        ContentType cType = _contentTypeExtensionPoint.getExtension(contentTypeId);
275
276        boolean isInfoEnabled = getLogger().isInfoEnabled();
277        
278        int nbTotalContent = (int) contents.getSize();
279        if (isInfoEnabled && nbTotalContent != 0)
280        {
281            List<String> i18nParams = new ArrayList<>();
282            i18nParams.add(String.valueOf(nbTotalContent));
283            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_NB_CONTENT_MAX", i18nParams)));
284        }
285        
286        int nbContent = 0;
287        int pourcentMax = 10;
288        for (Content content : contents)
289        {
290            List<String> labels = Arrays.asList(((VersionableAmetysObject) content).getAllLabels());
291            boolean exportContent = true;
292            if (_exportOnlyValidatedContent)
293            {
294                if (labels.contains(CmsConstants.LIVE_LABEL))
295                {
296                    ((VersionableAmetysObject) content).switchToLabel(CmsConstants.LIVE_LABEL);
297                }
298                else
299                {
300                    exportContent = false;
301                }
302            }
303            
304            if (exportContent)
305            {
306                String id = content.getId();
307                PreparedStatement stmt = null;
308                try 
309                {
310                    stmt = getInsertPreparedStatementFromTableName(_sqlTablePrefix, tableName, _mappingPolicy);
311                    stmt.setString(1, id);
312                    
313                    getLogger().debug(" with id: {}", id);
314                    
315                    ExportCounter fillIndex = new ExportCounter(2);
316                    fillValues(fillIndex, cType, content.getDataHolder(), tableName, "", id, stmt);
317                    fillAdditionalData(fillIndex, content, stmt);
318                    stmt.execute();
319                }
320                finally
321                {
322                    ConnectionHelper.cleanup(stmt);
323                }
324                
325                _fillContentTable(id, tableName);
326                executeInsert();
327                
328                int pourcent = nbContent * 100 / nbTotalContent;
329                if (pourcent >= pourcentMax)
330                {
331                    if (isInfoEnabled)
332                    {
333                        List<String> i18nParams = new ArrayList<>();
334                        i18nParams.add(String.valueOf(pourcentMax));
335                        getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_NB_CONTENT_FILL", i18nParams)));
336                    }
337                    pourcentMax += 10;
338                }
339                
340                nbContent++;
341            }
342        }
343        
344        if (isInfoEnabled && nbTotalContent != 0)
345        {
346            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_NB_CONTENT_FILL_MAX")));
347        }
348    }
349    
350    private void _fillContentTable(String idContent, String tableName) throws SQLException
351    {
352        String dataTableName = _sqlPrefixConf + ExportManager.CONTENT_TABLE_NAME;
353        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlPrefixConf, dataTableName, "FULL");
354        _stmtList.add(stmt);
355        
356        stmt.setString(1, idContent);
357        stmt.setString(2, tableName);
358
359        stmt.addBatch();
360        
361        if (getLogger().isDebugEnabled())
362        {
363            getLogger().debug(stmt.toString());
364        }
365    }
366    
367    /**
368     * Fill values from a composite metadata
369     * @param fillIndex the position indicator in the insert statement 
370     * @param modelItemContainer the attribute definitions
371     * @param dataHolder the {@link ModelAwareDataHolder}
372     * @param tableName the table name
373     * @param columnNamePrefix the column name prefix
374     * @param id the content id
375     * @param stmt the jdbc statement
376     * @throws SQLException if a sql error occurred
377     * @throws AmetysRepositoryException if an ametys repository error occurred
378     * @throws IOException if an IO error occurred
379     */
380    protected void fillValues(ExportCounter fillIndex, ModelItemContainer modelItemContainer, ModelAwareDataHolder dataHolder, String tableName, String columnNamePrefix, String id, PreparedStatement stmt) throws SQLException, AmetysRepositoryException, IOException
381    {
382        for (ModelItem modelItem : modelItemContainer.getModelItems())
383        {
384            String name = modelItem.getName();
385            String columnName = columnNamePrefix + name;
386            if (modelItem instanceof ElementDefinition definition)
387            {
388                // simple element
389                if (definition.isMultiple() && !_exportNoMultiValuedTable)
390                {
391                    if (dataHolder != null && dataHolder.hasValue(name))
392                    {
393                        fillTableForMultipleAttribute(definition, dataHolder, name, tableName + "_" + columnName, id);
394                    }
395                }
396                else
397                {
398                    fillValue(fillIndex, definition, dataHolder, name, id, stmt);
399                }
400            }
401            else if (modelItem instanceof CompositeDefinition)
402            {
403                // composite
404                ModelAwareComposite composite = dataHolder != null ? dataHolder.getValue(name) : null;
405                fillValues(fillIndex, (CompositeDefinition) modelItem, composite, tableName, columnName + "_", id, stmt);
406            }
407            else if (modelItem instanceof RepeaterDefinition)
408            {
409                // repeater
410                if (dataHolder != null)
411                {
412                    ModelAwareRepeater repeater = dataHolder.getRepeater(name);
413                    if (repeater != null)
414                    {
415                        fillTableForRepeater((RepeaterDefinition) modelItem, dataHolder.getRepeater(name), tableName + "_" + columnName, id);
416                    }
417                }
418            }
419        }
420    }
421
422    /**
423     * Fill values from a repeater
424     * @param definition the repeater definition
425     * @param repeater the repeater
426     * @param tableName the table name
427     * @param id the content id
428     * @throws SQLException if a sql error occurred
429     * @throws AmetysRepositoryException if an ametys repository error occurred
430     * @throws IOException if an IO error occurred
431     */
432    protected void fillTableForRepeater(RepeaterDefinition definition, ModelAwareRepeater repeater, String tableName, String id) throws SQLException, AmetysRepositoryException, IOException
433    {
434        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlTablePrefix, tableName, _mappingPolicy);
435        _stmtList.add(stmt);
436        
437        for (ModelAwareRepeaterEntry entry : repeater.getEntries())
438        {
439            int position = entry.getPosition();
440            String idRepeater = id + "@" + position;
441
442            stmt.setString(1, idRepeater);
443            stmt.setString(2, id);
444            stmt.setInt(3, position);
445            
446            ExportCounter fillIndex = new ExportCounter(4);
447
448            fillValues(fillIndex, definition, entry, tableName, "", idRepeater, stmt);
449            
450            if (getLogger().isDebugEnabled())
451            {
452                getLogger().debug(stmt.toString());
453            }
454            
455            stmt.addBatch();
456        }
457    }
458    
459    /**
460     * Fill values from multiple attribute
461     * @param definition the attribute definition
462     * @param dataHolder the {@link ModelAwareDataHolder}
463     * @param name the attribute name
464     * @param tableName the table name
465     * @param id the content id
466     * @throws SQLException if a sql error occurred
467     * @throws IOException if an IO error occurred
468     * @throws AmetysRepositoryException if an ametys repository error occurred
469     */
470    protected void fillTableForMultipleAttribute(ElementDefinition definition, ModelAwareDataHolder dataHolder, String name, String tableName, String id) throws SQLException, AmetysRepositoryException, IOException
471    {
472        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlTablePrefix, tableName, _mappingPolicy);
473        _stmtList.add(stmt);
474        
475        stmt.setString(1, id);
476        
477        ExportCounter fillIndex = new ExportCounter(2);
478        
479        fillValue(fillIndex, definition, dataHolder, name, id, stmt);
480    }
481    
482    /**
483     * Fill values from an attribute
484     * @param fillIndex the position indicator in the insert statement 
485     * @param definition the metadata definition model
486     * @param dataHolder the {@link ModelAwareDataHolder}
487     * @param name the attribute name
488     * @param id the content id
489     * @param stmt the jdbc statement
490     * @throws SQLException if a sql error occurred
491     * @throws AmetysRepositoryException if an ametys repository error occurred
492     * @throws IOException if an IO error occurred
493     */
494    protected void fillValue(ExportCounter fillIndex, ElementDefinition definition, ModelAwareDataHolder dataHolder, String name, String id, PreparedStatement stmt) throws SQLException, AmetysRepositoryException, IOException
495    {
496        String type = definition.getType().getId();
497        Object value = dataHolder != null ? dataHolder.getValue(name) : null;
498        if (definition.isMultiple())
499        {
500            if (_exportNoMultiValuedTable)
501            {
502                String multipleValue = "";
503                if (value != null)
504                {
505                    Object[] values = (Object[]) value;
506                    for (int i = 0; i < values.length; i++)
507                    {
508                        if (i != 0)
509                        {
510                            multipleValue += _separator;
511                        }
512                         
513                        multipleValue += _getValueForNoMultiValuedTable(values[i], type);
514                    }
515                }
516
517                if (StringUtils.isNotBlank(multipleValue))
518                {
519                    stmt.setString(fillIndex.getCount(), multipleValue);
520                }
521                else
522                {
523                    stmt.setNull(fillIndex.getCount(), java.sql.Types.VARCHAR);
524                }
525                
526                fillIndex.incrementCount();
527            }
528            else
529            {
530                int position = 1;
531                if (value != null)
532                {
533                    Object[] values = (Object[]) value;
534                    for (Object o : values)
535                    {
536                        int nbPos = _setValue(o, 2, stmt, type, name, id);
537                        stmt.setInt(2 + nbPos, position);
538                        position++;
539                        
540                        if (getLogger().isDebugEnabled())
541                        {
542                            getLogger().debug(stmt.toString());
543                        }
544                        
545                        stmt.addBatch();
546                    }
547                }
548            }
549        }
550        else
551        {
552            if (value != null)
553            {
554                int nbPos = _setValue(value, fillIndex.getCount(), stmt, type, name, id);
555                fillIndex.incrementCount(nbPos);
556            }
557            else
558            {
559                int nbPos = _setNull(fillIndex.getCount(), stmt, type);
560                fillIndex.incrementCount(nbPos);
561            }
562        }
563    }
564    
565    private int _setValue(Object value, int position, PreparedStatement stmt, String type, String name, String contentId) throws SQLException, IOException
566    {
567        switch (type)
568        {
569            case ModelItemTypeConstants.CONTENT_ELEMENT_TYPE_ID:
570                stmt.setString(position, ((ContentValue) value).getContentId());
571                return 1;
572            case org.ametys.runtime.model.type.ModelItemTypeConstants.LONG_TYPE_ID:
573                stmt.setLong(position, (long) value);
574                return 1;
575            case org.ametys.runtime.model.type.ModelItemTypeConstants.BOOLEAN_TYPE_ID:
576                stmt.setBoolean(position, (boolean) value);
577                return 1;
578            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATE_TYPE_ID:
579                stmt.setDate(position, java.sql.Date.valueOf((LocalDate) value));
580                return 1;
581            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATETIME_TYPE_ID:
582                stmt.setTimestamp(position, Timestamp.from(((ZonedDateTime) value).toInstant()));
583                return 1;
584            case org.ametys.runtime.model.type.ModelItemTypeConstants.DOUBLE_TYPE_ID:
585                stmt.setDouble(position, (double) value);
586                return 1;
587            case ModelItemTypeConstants.USER_ELEMENT_TYPE_ID:
588                UserIdentity user = (UserIdentity) value;
589                stmt.setString(position, user.getLogin());
590                stmt.setString(position + 1, user.getPopulationId());
591                return 2;
592            case ModelItemTypeConstants.GEOCODE_ELEMENT_TYPE_ID:
593                Geocode geocode = (Geocode) value;
594                stmt.setDouble(position, geocode.getLongitude());
595                stmt.setDouble(position + 1, geocode.getLatitude());
596                return 2;
597            case ModelItemTypeConstants.MULTILINGUAL_STRING_ELEMENT_TYPE_ID:
598                MultilingualString multilingualString = (MultilingualString) value;
599                int i = 0;
600                for (String lang : _languageManager.getAvailableLanguages().keySet())
601                {
602                    Locale locale = new Locale(lang);
603                    if (multilingualString.hasLocale(locale))
604                    {
605                        stmt.setString(position + i, multilingualString.getValue(locale));
606                    }
607                    else
608                    {
609                        stmt.setNull(position + i, Types.VARCHAR);
610                    }
611                    
612                    i++;
613                }
614                return i;
615            case ModelItemTypeConstants.BINARY_ELEMENT_TYPE_ID:
616            case ModelItemTypeConstants.FILE_ELEMENT_TYPE_ID:
617                File file = (File) value;
618                stmt.setString(position, file.getName());
619                stmt.setBlob(position + 1, AutoCloseInputStream.builder().setInputStream(file.getInputStream()).get());
620                stmt.setString(position + 2, file.getMimeType());
621                stmt.setLong(position + 3, file.getLength());
622                stmt.setTimestamp(position + 4, Timestamp.from(file.getLastModificationDate().toInstant()));
623                return 5;
624            case ModelItemTypeConstants.RICH_TEXT_ELEMENT_TYPE_ID:
625                RichText richText = (RichText) value;
626                String richTextValue;
627                try (InputStream is = richText.getInputStream())
628                {
629                    richTextValue = IOUtils.toString(is, "UTF-8").replaceAll(_EXCLUDE_XML_TAGS, "");
630                    stmt.setString(position, richTextValue);
631                }
632                catch (IOException e)
633                {
634                    getLogger().warn("Error with richText of attribute '{}' of content '{}'.", name, contentId);
635                    stmt.setNull(position, java.sql.Types.BLOB);
636                }
637                
638                fillColumnForRichTextData(richText, name, contentId);
639
640                return 1;
641            case org.ametys.runtime.model.type.ModelItemTypeConstants.STRING_TYPE_ID:
642            default:
643                stmt.setString(position, value.toString());
644                return 1;
645        }
646    }
647    
648    private int _setNull(int position, PreparedStatement stmt, String type) throws SQLException
649    {
650        switch (type)
651        {
652            case org.ametys.runtime.model.type.ModelItemTypeConstants.LONG_TYPE_ID:
653                stmt.setNull(position, Types.INTEGER);
654                return 1;
655            case org.ametys.runtime.model.type.ModelItemTypeConstants.BOOLEAN_TYPE_ID:
656                stmt.setNull(position, Types.NULL);
657                return 1;
658            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATE_TYPE_ID:
659            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATETIME_TYPE_ID:
660                stmt.setNull(position, Types.DATE);
661                return 1;
662            case org.ametys.runtime.model.type.ModelItemTypeConstants.DOUBLE_TYPE_ID:
663                stmt.setNull(position, Types.DOUBLE);
664                return 1;
665            case ModelItemTypeConstants.USER_ELEMENT_TYPE_ID:
666                stmt.setNull(position, Types.VARCHAR);
667                stmt.setNull(position + 1, Types.VARCHAR);
668                return 2;
669            case ModelItemTypeConstants.GEOCODE_ELEMENT_TYPE_ID:
670                stmt.setNull(position, Types.DOUBLE);
671                stmt.setNull(position + 1, Types.DOUBLE);
672                return 2;
673            case ModelItemTypeConstants.MULTILINGUAL_STRING_ELEMENT_TYPE_ID:
674                Set<String> availableLanguages = _languageManager.getAvailableLanguages().keySet();
675                for (int i = 0; i < availableLanguages.size(); i++)
676                {
677                    stmt.setNull(position + i, Types.VARCHAR);
678                }
679                return availableLanguages.size();
680            case ModelItemTypeConstants.BINARY_ELEMENT_TYPE_ID:
681            case ModelItemTypeConstants.FILE_ELEMENT_TYPE_ID:
682                stmt.setNull(position, java.sql.Types.VARCHAR);
683                stmt.setNull(position + 1, java.sql.Types.BLOB);
684                stmt.setNull(position + 2, java.sql.Types.VARCHAR);
685                stmt.setNull(position + 3, java.sql.Types.INTEGER);
686                stmt.setNull(position + 4, java.sql.Types.DATE);
687                return 5;
688            case ModelItemTypeConstants.RICH_TEXT_ELEMENT_TYPE_ID:
689                stmt.setNull(position, java.sql.Types.BLOB);
690                return 1;
691            case org.ametys.runtime.model.type.ModelItemTypeConstants.STRING_TYPE_ID:
692            case ModelItemTypeConstants.CONTENT_ELEMENT_TYPE_ID:
693            default:
694                stmt.setNull(position, Types.VARCHAR);
695                return 1;
696        }
697    }
698    
699    private String _getValueForNoMultiValuedTable(Object value, String type)
700    {
701        if (ModelItemTypeConstants.USER_ELEMENT_TYPE_ID.equals(type))
702        {
703            User user = _userManager.getUser((UserIdentity) value);
704            if (user != null)
705            {
706                return user.getFullName();
707            }
708            else
709            {
710                return UserIdentity.userIdentityToString((UserIdentity) value);
711            }
712        }
713        else
714        {
715            return value.toString();
716        }
717    }
718
719    /**
720     * Add additional values for content (Title, type, language, creator, creationDate, ...)
721     * @param fillIndex the position indicator in the insert statement 
722     * @param content the content
723     * @param stmt the jdbc statement
724     * @throws SQLException if a sql error occurred
725     * @throws AmetysRepositoryException if an ametys repository error occurred
726     */
727    protected void fillAdditionalData(ExportCounter fillIndex, Content content, PreparedStatement stmt) throws AmetysRepositoryException, SQLException
728    {
729        if (content != null)
730        {
731            stmt.setString(fillIndex.getCount(), content.getTitle());
732            fillIndex.incrementCount();
733            
734            stmt.setString(fillIndex.getCount(), content.getTypes()[0]);
735            fillIndex.incrementCount();
736            
737            //TODO site for webContent
738            
739            stmt.setString(fillIndex.getCount(), content.getLanguage());
740            fillIndex.incrementCount();
741            
742            stmt.setString(fillIndex.getCount(), content.getCreator().getLogin());
743            fillIndex.incrementCount();
744            
745            if (content.getCreationDate() != null)
746            {
747                java.sql.Date sqlCreationDate = new java.sql.Date(content.getCreationDate().toInstant().toEpochMilli());
748                stmt.setDate(fillIndex.getCount(), sqlCreationDate);
749            }
750            else
751            {
752                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
753            }
754            fillIndex.incrementCount();
755            
756            stmt.setString(fillIndex.getCount(), content.getLastContributor().getLogin());
757            fillIndex.incrementCount();
758            
759            if (content.getLastModified() != null)
760            {
761                java.sql.Date sqlLastModificationDate = new java.sql.Date(content.getLastModified().toInstant().toEpochMilli());
762                stmt.setDate(fillIndex.getCount(), sqlLastModificationDate);
763            }
764            else
765            {
766                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
767            }
768            fillIndex.incrementCount();
769            
770            if (content.getLastValidationDate() != null)
771            {
772                java.sql.Date sqlLastValidationDate = new java.sql.Date(content.getLastValidationDate().toInstant().toEpochMilli());
773                stmt.setDate(fillIndex.getCount(), sqlLastValidationDate);
774            }
775            else
776            {
777                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
778            }
779            fillIndex.incrementCount();
780            
781            if (content.getLastMajorValidationDate() != null)
782            {
783                java.sql.Date sqlLastMajorValidationDate = new java.sql.Date(content.getLastMajorValidationDate().toInstant().toEpochMilli());
784                stmt.setDate(fillIndex.getCount(), sqlLastMajorValidationDate);
785            }
786            else
787            {
788                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
789            }
790            fillIndex.incrementCount();
791        }
792    }
793
794    /**
795     * Fill column for data in rich text
796     * @param richText the rich text
797     * @param attributeName the metadata name
798     * @param contentId the content id
799     * @throws AmetysRepositoryException if an error occurred
800     * @throws SQLException if a sql error occurred
801     * @throws IOException if an IO error occurred
802     */
803    protected void fillColumnForRichTextData(RichText richText, String attributeName, String contentId) throws AmetysRepositoryException, SQLException, IOException
804    {
805        String dataTableName = _sqlPrefixConf + ExportManager.RICH_TEXT_DATA_TABLE_NAME;
806        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlPrefixConf, dataTableName, "FULL");
807        _stmtList.add(stmt);
808        
809        int position = 1;
810        for (NamedResource resource : richText.getAttachments())
811        {
812            String id = contentId + "@" + attributeName + ";" + resource.getFilename();
813            stmt.setString(1, id);
814            stmt.setString(2, contentId);
815            stmt.setString(3, attributeName);
816            stmt.setString(4, resource.getFilename());
817            stmt.setBlob(5, AutoCloseInputStream.builder().setInputStream(resource.getInputStream()).get());
818            stmt.setString(6, resource.getMimeType());
819            stmt.setLong(7, resource.getLength());
820            stmt.setTimestamp(8, Timestamp.from(resource.getLastModificationDate().toInstant()));
821            stmt.setInt(9, position);
822            position++;
823            
824            if (getLogger().isDebugEnabled())
825            {
826                getLogger().debug(stmt.toString());
827            }
828            
829            stmt.addBatch();
830        }
831    }
832
833    /**
834     * Prepare INSERT statement
835     * @param prefix the table prefix
836     * @param tableName the table name
837     * @param mappingPolicy the mapping policy
838     * @return the INSERT preparedStatement
839     * @throws SQLException if a sql error occurred
840     */
841    protected PreparedStatement getInsertPreparedStatementFromTableName(String prefix, String tableName, String mappingPolicy) throws SQLException
842    {
843        StringBuilder sql = new StringBuilder();
844        sql.append("INSERT INTO ");
845        sql.append(_normalizeNameComponent.normalizedTableName(prefix, mappingPolicy, tableName, _connection));
846        sql.append(" VALUES ( ?");
847        
848        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
849
850        for (int i = 1; i < tableInfo.getNbColumns(); i++)
851        {
852            sql.append(", ?");
853        }
854        sql.append(")");
855        
856        if (getLogger().isDebugEnabled())
857        {
858            getLogger().debug("Executing: {}", sql.toString());
859        }
860        
861        PreparedStatement stmt = _connection.prepareStatement(sql.toString());
862        return stmt;
863    }
864
865    /**
866     * Get the name of SQL table for given content type
867     * @param cTypeId The id of content type
868     * @return the name of SQL table or null if not found
869     */
870    protected String getTableName (String cTypeId)
871    {
872        return _contentTypesToExport.get(cTypeId);
873    }
874}