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     */
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 definition)
383            {
384                // simple element
385                if (definition.isMultiple() && !_exportNoMultiValuedTable)
386                {
387                    if (dataHolder != null && dataHolder.hasValue(name))
388                    {
389                        fillTableForMultipleAttribute(definition, dataHolder, name, tableName + "_" + columnName, id);
390                    }
391                }
392                else
393                {
394                    fillValue(fillIndex, definition, dataHolder, name, id, stmt);
395                }
396            }
397            else if (modelItem instanceof CompositeDefinition)
398            {
399                // composite
400                ModelAwareComposite composite = dataHolder != null ? dataHolder.getValue(name) : null;
401                fillValues(fillIndex, (CompositeDefinition) modelItem, composite, tableName, columnName + "_", id, stmt);
402            }
403            else if (modelItem instanceof RepeaterDefinition)
404            {
405                // repeater
406                if (dataHolder != null)
407                {
408                    ModelAwareRepeater repeater = dataHolder.getRepeater(name);
409                    if (repeater != null)
410                    {
411                        fillTableForRepeater((RepeaterDefinition) modelItem, dataHolder.getRepeater(name), tableName + "_" + columnName, id);
412                    }
413                }
414            }
415        }
416    }
417
418    /**
419     * Fill values from a repeater
420     * @param definition the repeater definition
421     * @param repeater the repeater
422     * @param tableName the table name
423     * @param id the content id
424     * @throws SQLException if a sql error occurred
425     * @throws AmetysRepositoryException if an ametys repository error occurred
426     */
427    protected void fillTableForRepeater(RepeaterDefinition definition, ModelAwareRepeater repeater, String tableName, String id) throws SQLException, AmetysRepositoryException
428    {
429        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlTablePrefix, tableName, _mappingPolicy);
430        _stmtList.add(stmt);
431        
432        for (ModelAwareRepeaterEntry entry : repeater.getEntries())
433        {
434            int position = entry.getPosition();
435            String idRepeater = id + "@" + position;
436
437            stmt.setString(1, idRepeater);
438            stmt.setString(2, id);
439            stmt.setInt(3, position);
440            
441            ExportCounter fillIndex = new ExportCounter(4);
442
443            fillValues(fillIndex, definition, entry, tableName, "", idRepeater, stmt);
444            
445            if (getLogger().isDebugEnabled())
446            {
447                getLogger().debug(stmt.toString());
448            }
449            
450            stmt.addBatch();
451        }
452    }
453    
454    /**
455     * Fill values from multiple attribute
456     * @param definition the attribute definition
457     * @param dataHolder the {@link ModelAwareDataHolder}
458     * @param name the attribute name
459     * @param tableName the table name
460     * @param id the content id
461     * @throws SQLException if a sql error occurred
462     */
463    protected void fillTableForMultipleAttribute(ElementDefinition definition, ModelAwareDataHolder dataHolder, String name, String tableName, String id) throws SQLException
464    {
465        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlTablePrefix, tableName, _mappingPolicy);
466        _stmtList.add(stmt);
467        
468        stmt.setString(1, id);
469        
470        ExportCounter fillIndex = new ExportCounter(2);
471        
472        fillValue(fillIndex, definition, dataHolder, name, id, stmt);
473    }
474    
475    /**
476     * Fill values from an attribute
477     * @param fillIndex the position indicator in the insert statement 
478     * @param definition the metadata definition model
479     * @param dataHolder the {@link ModelAwareDataHolder}
480     * @param name the attribute name
481     * @param id the content id
482     * @param stmt the jdbc statement
483     * @throws SQLException if a sql error occurred
484     * @throws AmetysRepositoryException if an ametys repository error occurred
485     */
486    protected void fillValue(ExportCounter fillIndex, ElementDefinition definition, ModelAwareDataHolder dataHolder, String name, String id, PreparedStatement stmt) throws SQLException, AmetysRepositoryException
487    {
488        String type = definition.getType().getId();
489        Object value = dataHolder != null ? dataHolder.getValue(name) : null;
490        if (definition.isMultiple())
491        {
492            if (_exportNoMultiValuedTable)
493            {
494                String multipleValue = "";
495                if (value != null)
496                {
497                    Object[] values = (Object[]) value;
498                    for (int i = 0; i < values.length; i++)
499                    {
500                        if (i != 0)
501                        {
502                            multipleValue += _separator;
503                        }
504                         
505                        multipleValue += _getValueForNoMultiValuedTable(values[i], type);
506                    }
507                }
508
509                if (StringUtils.isNotBlank(multipleValue))
510                {
511                    stmt.setString(fillIndex.getCount(), multipleValue);
512                }
513                else
514                {
515                    stmt.setNull(fillIndex.getCount(), java.sql.Types.VARCHAR);
516                }
517                
518                fillIndex.incrementCount();
519            }
520            else
521            {
522                int position = 1;
523                if (value != null)
524                {
525                    Object[] values = (Object[]) value;
526                    for (Object o : values)
527                    {
528                        int nbPos = _setValue(o, 2, stmt, type, name, id);
529                        stmt.setInt(2 + nbPos, position);
530                        position++;
531                        
532                        if (getLogger().isDebugEnabled())
533                        {
534                            getLogger().debug(stmt.toString());
535                        }
536                        
537                        stmt.addBatch();
538                    }
539                }
540            }
541        }
542        else
543        {
544            if (value != null)
545            {
546                int nbPos = _setValue(value, fillIndex.getCount(), stmt, type, name, id);
547                fillIndex.incrementCount(nbPos);
548            }
549            else
550            {
551                int nbPos = _setNull(fillIndex.getCount(), stmt, type);
552                fillIndex.incrementCount(nbPos);
553            }
554        }
555    }
556    
557    private int _setValue(Object value, int position, PreparedStatement stmt, String type, String name, String contentId) throws SQLException
558    {
559        switch (type)
560        {
561            case org.ametys.runtime.model.type.ModelItemTypeConstants.STRING_TYPE_ID:
562                stmt.setString(position, (String) value);
563                return 1;
564            case ModelItemTypeConstants.CONTENT_ELEMENT_TYPE_ID:
565                stmt.setString(position, ((ContentValue) value).getContentId());
566                return 1;
567            case org.ametys.runtime.model.type.ModelItemTypeConstants.LONG_TYPE_ID:
568                stmt.setLong(position, (long) value);
569                return 1;
570            case org.ametys.runtime.model.type.ModelItemTypeConstants.BOOLEAN_TYPE_ID:
571                stmt.setBoolean(position, (boolean) value);
572                return 1;
573            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATE_TYPE_ID:
574                stmt.setDate(position, java.sql.Date.valueOf((LocalDate) value));
575                return 1;
576            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATETIME_TYPE_ID:
577                stmt.setTimestamp(position, Timestamp.from(((ZonedDateTime) value).toInstant()));
578                return 1;
579            case org.ametys.runtime.model.type.ModelItemTypeConstants.DOUBLE_TYPE_ID:
580                stmt.setDouble(position, (double) value);
581                return 1;
582            case ModelItemTypeConstants.USER_ELEMENT_TYPE_ID:
583                UserIdentity user = (UserIdentity) value;
584                stmt.setString(position, user.getLogin());
585                stmt.setString(position + 1, user.getPopulationId());
586                return 2;
587            case ModelItemTypeConstants.GEOCODE_ELEMENT_TYPE_ID:
588                Geocode geocode = (Geocode) value;
589                stmt.setDouble(position, geocode.getLongitude());
590                stmt.setDouble(position + 1, geocode.getLatitude());
591                return 2;
592            case ModelItemTypeConstants.MULTILINGUAL_STRING_ELEMENT_TYPE_ID:
593                MultilingualString multilingualString = (MultilingualString) value;
594                int i = 0;
595                for (String lang : _languageManager.getAvailableLanguages().keySet())
596                {
597                    Locale locale = new Locale(lang);
598                    if (multilingualString.hasLocale(locale))
599                    {
600                        stmt.setString(position + i, multilingualString.getValue(locale));
601                    }
602                    else
603                    {
604                        stmt.setNull(position + i, Types.VARCHAR);
605                    }
606                    
607                    i++;
608                }
609                return i;
610            case ModelItemTypeConstants.BINARY_ELEMENT_TYPE_ID:
611            case ModelItemTypeConstants.FILE_ELEMENT_TYPE_ID:
612                File file = (File) value;
613                stmt.setString(position, file.getName());
614                stmt.setBlob(position + 1, new AutoCloseInputStream(file.getInputStream()));
615                stmt.setString(position + 2, file.getMimeType());
616                stmt.setLong(position + 3, file.getLength());
617                stmt.setTimestamp(position + 4, Timestamp.from(file.getLastModificationDate().toInstant()));
618                return 5;
619            case ModelItemTypeConstants.RICH_TEXT_ELEMENT_TYPE_ID:
620                RichText richText = (RichText) value;
621                String richTextValue;
622                try (InputStream is = richText.getInputStream())
623                {
624                    richTextValue = IOUtils.toString(is, "UTF-8").replaceAll(_EXCLUDE_XML_TAGS, "");
625                    stmt.setString(position, richTextValue);
626                }
627                catch (IOException e)
628                {
629                    getLogger().warn("Error with richText of attribute '{}' of content '{}'.", name, contentId);
630                    stmt.setNull(position, java.sql.Types.BLOB);
631                }
632                
633                fillColumnForRichTextData(richText, name, contentId);
634
635                return 1;
636            default:
637                throw new IllegalArgumentException("Unknown type " + type);
638        }
639    }
640    
641    private int _setNull(int position, PreparedStatement stmt, String type) throws SQLException
642    {
643        switch (type)
644        {
645            case org.ametys.runtime.model.type.ModelItemTypeConstants.STRING_TYPE_ID:
646            case ModelItemTypeConstants.CONTENT_ELEMENT_TYPE_ID:
647                stmt.setNull(position, Types.VARCHAR);
648                return 1;
649            case org.ametys.runtime.model.type.ModelItemTypeConstants.LONG_TYPE_ID:
650                stmt.setNull(position, Types.INTEGER);
651                return 1;
652            case org.ametys.runtime.model.type.ModelItemTypeConstants.BOOLEAN_TYPE_ID:
653                stmt.setNull(position, Types.NULL);
654                return 1;
655            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATE_TYPE_ID:
656            case org.ametys.runtime.model.type.ModelItemTypeConstants.DATETIME_TYPE_ID:
657                stmt.setNull(position, Types.DATE);
658                return 1;
659            case org.ametys.runtime.model.type.ModelItemTypeConstants.DOUBLE_TYPE_ID:
660                stmt.setNull(position, Types.DOUBLE);
661                return 1;
662            case ModelItemTypeConstants.USER_ELEMENT_TYPE_ID:
663                stmt.setNull(position, Types.VARCHAR);
664                stmt.setNull(position + 1, Types.VARCHAR);
665                return 2;
666            case ModelItemTypeConstants.GEOCODE_ELEMENT_TYPE_ID:
667                stmt.setNull(position, Types.DOUBLE);
668                stmt.setNull(position + 1, Types.DOUBLE);
669                return 2;
670            case ModelItemTypeConstants.MULTILINGUAL_STRING_ELEMENT_TYPE_ID:
671                Set<String> availableLanguages = _languageManager.getAvailableLanguages().keySet();
672                for (int i = 0; i < availableLanguages.size(); i++)
673                {
674                    stmt.setNull(position + i, Types.VARCHAR);
675                }
676                return availableLanguages.size();
677            case ModelItemTypeConstants.BINARY_ELEMENT_TYPE_ID:
678            case ModelItemTypeConstants.FILE_ELEMENT_TYPE_ID:
679                stmt.setNull(position, java.sql.Types.VARCHAR);
680                stmt.setNull(position + 1, java.sql.Types.BLOB);
681                stmt.setNull(position + 2, java.sql.Types.VARCHAR);
682                stmt.setNull(position + 3, java.sql.Types.INTEGER);
683                stmt.setNull(position + 4, java.sql.Types.DATE);
684                return 5;
685            case ModelItemTypeConstants.RICH_TEXT_ELEMENT_TYPE_ID:
686                stmt.setNull(position, java.sql.Types.BLOB);
687                return 1;
688            default:
689                throw new IllegalArgumentException("Unknown type " + type);
690        }
691    }
692    
693    private String _getValueForNoMultiValuedTable(Object value, String type)
694    {
695        if (ModelItemTypeConstants.USER_ELEMENT_TYPE_ID.equals(type))
696        {
697            User user = _userManager.getUser((UserIdentity) value);
698            if (user != null)
699            {
700                return user.getFullName();
701            }
702            else
703            {
704                return UserIdentity.userIdentityToString((UserIdentity) value);
705            }
706        }
707        else
708        {
709            return value.toString();
710        }
711    }
712
713    /**
714     * Add additional values for content (Title, type, language, creator, creationDate, ...)
715     * @param fillIndex the position indicator in the insert statement 
716     * @param content the content
717     * @param stmt the jdbc statement
718     * @throws SQLException if a sql error occurred
719     * @throws AmetysRepositoryException if an ametys repository error occurred
720     */
721    protected void fillAdditionalData(ExportCounter fillIndex, Content content, PreparedStatement stmt) throws AmetysRepositoryException, SQLException
722    {
723        if (content != null)
724        {
725            stmt.setString(fillIndex.getCount(), content.getTitle());
726            fillIndex.incrementCount();
727            
728            stmt.setString(fillIndex.getCount(), content.getTypes()[0]);
729            fillIndex.incrementCount();
730            
731            //TODO site for webContent
732            
733            stmt.setString(fillIndex.getCount(), content.getLanguage());
734            fillIndex.incrementCount();
735            
736            stmt.setString(fillIndex.getCount(), content.getCreator().getLogin());
737            fillIndex.incrementCount();
738            
739            if (content.getCreationDate() != null)
740            {
741                java.sql.Date sqlCreationDate = new java.sql.Date(content.getCreationDate().toInstant().toEpochMilli());
742                stmt.setDate(fillIndex.getCount(), sqlCreationDate);
743            }
744            else
745            {
746                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
747            }
748            fillIndex.incrementCount();
749            
750            stmt.setString(fillIndex.getCount(), content.getLastContributor().getLogin());
751            fillIndex.incrementCount();
752            
753            if (content.getLastModified() != null)
754            {
755                java.sql.Date sqlLastModificationDate = new java.sql.Date(content.getLastModified().toInstant().toEpochMilli());
756                stmt.setDate(fillIndex.getCount(), sqlLastModificationDate);
757            }
758            else
759            {
760                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
761            }
762            fillIndex.incrementCount();
763            
764            if (content.getLastValidationDate() != null)
765            {
766                java.sql.Date sqlLastValidationDate = new java.sql.Date(content.getLastValidationDate().toInstant().toEpochMilli());
767                stmt.setDate(fillIndex.getCount(), sqlLastValidationDate);
768            }
769            else
770            {
771                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
772            }
773            fillIndex.incrementCount();
774            
775            if (content.getLastMajorValidationDate() != null)
776            {
777                java.sql.Date sqlLastMajorValidationDate = new java.sql.Date(content.getLastMajorValidationDate().toInstant().toEpochMilli());
778                stmt.setDate(fillIndex.getCount(), sqlLastMajorValidationDate);
779            }
780            else
781            {
782                stmt.setNull(fillIndex.getCount(), java.sql.Types.DATE);
783            }
784            fillIndex.incrementCount();
785        }
786    }
787
788    /**
789     * Fill column for data in rich text
790     * @param richText the rich text
791     * @param attributeName the metadata name
792     * @param contentId the content id
793     * @throws AmetysRepositoryException if an error occurred
794     * @throws SQLException if a sql error occurred
795     */
796    protected void fillColumnForRichTextData(RichText richText, String attributeName, String contentId) throws AmetysRepositoryException, SQLException
797    {
798        String dataTableName = _sqlPrefixConf + ExportManager.RICH_TEXT_DATA_TABLE_NAME;
799        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlPrefixConf, dataTableName, "FULL");
800        _stmtList.add(stmt);
801        
802        int position = 1;
803        for (NamedResource resource : richText.getAttachments())
804        {
805            String id = contentId + "@" + attributeName + ";" + resource.getFilename();
806            stmt.setString(1, id);
807            stmt.setString(2, contentId);
808            stmt.setString(3, attributeName);
809            stmt.setString(4, resource.getFilename());
810            stmt.setBlob(5, new AutoCloseInputStream(resource.getInputStream()));
811            stmt.setString(6, resource.getMimeType());
812            stmt.setLong(7, resource.getLength());
813            stmt.setTimestamp(8, Timestamp.from(resource.getLastModificationDate().toInstant()));
814            stmt.setInt(9, position);
815            position++;
816            
817            if (getLogger().isDebugEnabled())
818            {
819                getLogger().debug(stmt.toString());
820            }
821            
822            stmt.addBatch();
823        }
824    }
825
826    /**
827     * Prepare INSERT statement
828     * @param prefix the table prefix
829     * @param tableName the table name
830     * @param mappingPolicy the mapping policy
831     * @return the INSERT preparedStatement
832     * @throws SQLException if a sql error occurred
833     */
834    protected PreparedStatement getInsertPreparedStatementFromTableName(String prefix, String tableName, String mappingPolicy) throws SQLException
835    {
836        StringBuilder sql = new StringBuilder();
837        sql.append("INSERT INTO ");
838        sql.append(_normalizeNameComponent.normalizedTableName(prefix, mappingPolicy, tableName, _connection));
839        sql.append(" VALUES ( ?");
840        
841        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
842
843        for (int i = 1; i < tableInfo.getNbColumns(); i++)
844        {
845            sql.append(", ?");
846        }
847        sql.append(")");
848        
849        if (getLogger().isDebugEnabled())
850        {
851            getLogger().debug("Executing: {}", sql.toString());
852        }
853        
854        PreparedStatement stmt = _connection.prepareStatement(sql.toString());
855        return stmt;
856    }
857
858    /**
859     * Get the name of SQL table for given content type
860     * @param cTypeId The id of content type
861     * @return the name of SQL table or null if not found
862     */
863    protected String getTableName (String cTypeId)
864    {
865        return _contentTypesToExport.get(cTypeId);
866    }
867}