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