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