Source code

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.contenttype.ContentType;
045import org.ametys.cms.contenttype.ContentTypeExtensionPoint;
046import org.ametys.cms.data.ContentValue;
047import org.ametys.cms.data.File;
048import org.ametys.cms.data.Geocode;
049import org.ametys.cms.data.NamedResource;
050import org.ametys.cms.data.RichText;
051import org.ametys.cms.data.type.ModelItemTypeConstants;
052import org.ametys.cms.languages.LanguagesManager;
053import org.ametys.cms.repository.Content;
054import org.ametys.cms.repository.ContentQueryHelper;
055import org.ametys.cms.repository.ContentTypeExpression;
056import org.ametys.core.datasource.ConnectionHelper;
057import org.ametys.core.user.User;
058import org.ametys.core.user.UserIdentity;
059import org.ametys.core.user.UserManager;
060import org.ametys.core.util.I18nUtils;
061import org.ametys.plugins.repository.AmetysObjectIterable;
062import org.ametys.plugins.repository.AmetysObjectResolver;
063import org.ametys.plugins.repository.AmetysRepositoryException;
064import org.ametys.plugins.repository.data.holder.ModelAwareDataHolder;
065import org.ametys.plugins.repository.data.holder.group.impl.ModelAwareComposite;
066import org.ametys.plugins.repository.data.holder.group.impl.ModelAwareRepeater;
067import org.ametys.plugins.repository.data.holder.group.impl.ModelAwareRepeaterEntry;
068import org.ametys.plugins.repository.metadata.MultilingualString;
069import org.ametys.plugins.repository.model.CompositeDefinition;
070import org.ametys.plugins.repository.model.RepeaterDefinition;
071import org.ametys.plugins.repository.query.expression.Expression.Operator;
072import org.ametys.plugins.repository.version.VersionableAmetysObject;
073import org.ametys.runtime.config.Config;
074import org.ametys.runtime.i18n.I18nizableText;
075import org.ametys.runtime.model.ElementDefinition;
076import org.ametys.runtime.model.ModelItem;
077import org.ametys.runtime.model.ModelItemContainer;
078import org.ametys.runtime.plugin.component.AbstractLogEnabled;
079
080/**
081 * Fill sql table component
082 */
083public class FillSqlTableComponent extends AbstractLogEnabled implements Component, Serviceable
084{
085    /** The component role */
086    public static final String ROLE = FillSqlTableComponent.class.getName();
087    
088    private static final String _EXCLUDE_XML_TAGS = "<(.*?)>";
089    
090    /** Content type extension point. */
091    protected ContentTypeExtensionPoint _contentTypeExtensionPoint;
092  
093    /** The ametys object resolver. */
094    protected AmetysObjectResolver _resolver;
095  
096    /** The normalise name component. */
097    protected NormalizeNameComponent _normalizeNameComponent;
098    
099    /** The configured list of content types to export associated to the SQL table name to use */
100    protected Map<String, String> _contentTypesToExport;
101    
102    /** The i18n translator. */
103    protected I18nUtils _i18nTranslator;
104    
105    /** The language manager */
106    protected LanguagesManager _languageManager;
107    
108    /** The user manager */
109    protected UserManager _userManager;
110
111    private Connection _connection;
112    private String _sqlTablePrefix;
113    private String _sqlPrefixConf;
114    private Map<String, ExportTableInfo> _tablesInfos;
115    private String _mappingPolicy;
116    private LinkedList<PreparedStatement> _stmtList;
117    private boolean _exportOnlyValidatedContent;
118    private boolean _exportNoMultiValuedTable;
119    private String _separator;
120    
121    public void service(ServiceManager manager) throws ServiceException
122    {
123        _contentTypeExtensionPoint = (ContentTypeExtensionPoint) manager.lookup(ContentTypeExtensionPoint.ROLE);
124        _resolver = (AmetysObjectResolver) manager.lookup(AmetysObjectResolver.ROLE);
125        _normalizeNameComponent = (NormalizeNameComponent) manager.lookup(NormalizeNameComponent.ROLE);
126        _i18nTranslator = (I18nUtils) manager.lookup(I18nUtils.ROLE);
127        _languageManager = (LanguagesManager) manager.lookup(LanguagesManager.ROLE);
128        _userManager = (UserManager) manager.lookup(UserManager.ROLE);
129    }
130    
131    /**
132     * Fill table with contents
133     * @param exportConfiguration the content export configuration
134     * @param tableInfo the map of table information
135     * @throws SQLException if a sql error occurred
136     * @throws AmetysRepositoryException if an ametys repository error occurred
137     */
138    public synchronized void fillTable(ExportConfiguration exportConfiguration, Map<String, ExportTableInfo> tableInfo) throws SQLException, AmetysRepositoryException
139    {
140        // Get from configuration
141        _sqlTablePrefix = exportConfiguration.getTablePrefix();
142        _sqlPrefixConf = exportConfiguration.getTablePrefix();
143        _mappingPolicy = exportConfiguration.getMappingPolicy();
144        _contentTypesToExport = exportConfiguration.getContentTypesToExport();
145        _exportOnlyValidatedContent = exportConfiguration.exportOnlyValidatedContent();
146        _exportNoMultiValuedTable = exportConfiguration.exportNoMultiValuedTable();
147        _separator = exportConfiguration.getSeparator();
148
149        // Initialization
150        _tablesInfos = tableInfo;
151        _stmtList = new LinkedList<>();
152        
153        try
154        {
155            String datasourceId = Config.getInstance().getValue("org.ametys.plugins.contentio.content.export.datasource");
156            _connection = ConnectionHelper.getConnection(datasourceId); 
157            insertValues();
158        }
159        finally
160        {
161            ConnectionHelper.cleanup(_connection);
162            _sqlTablePrefix = null;
163            _sqlPrefixConf = null;
164            _mappingPolicy = null;
165            _contentTypesToExport = null;
166            _separator = null;
167            _stmtList = null;
168        }
169    }
170    
171    private void executeInsert() throws SQLException
172    {
173        for (PreparedStatement stmt : _stmtList)
174        {
175            try
176            {
177                if (getLogger().isDebugEnabled())
178                {
179                    getLogger().debug("Query : {}", stmt.toString());
180                }
181                stmt.executeBatch();
182            }
183            catch (SQLException e)
184            {
185                throw new SQLException(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_ERROR_SQL")), e);
186            }
187            finally
188            {
189                ConnectionHelper.cleanup(stmt);
190            }
191        }
192        
193        _stmtList.clear();
194    }
195
196    /**
197     * Fill the SQL tables with values from JCR
198     * @throws SQLException if a sql error occurred
199     * @throws AmetysRepositoryException if an ametys repository error occurred
200     */
201    protected void insertValues() throws SQLException, AmetysRepositoryException
202    {
203        boolean isInfoEnabled = getLogger().isInfoEnabled();
204        
205        int nbTotalTypeContenu = _contentTypesToExport.entrySet().size();
206        if (isInfoEnabled && nbTotalTypeContenu != 0)
207        {
208            List<String> i18nParams = new ArrayList<>();
209            i18nParams.add(String.valueOf(nbTotalTypeContenu));
210            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_ANALYZE_BEGIN", i18nParams)));
211        }
212        
213        int nbTypeContenu = 0;
214        int pourcentMax = 10;
215        for (Entry<String, String> entry : _contentTypesToExport.entrySet()) 
216        {
217            String contentTypeId = entry.getKey();
218            ContentType contentType = _contentTypeExtensionPoint.getExtension(contentTypeId);
219            
220            if (!contentType.isAbstract())
221            {
222                _sqlTablePrefix = entry.getValue();
223                
224                if (isInfoEnabled)
225                {
226                    List<String> i18nParams = new ArrayList<>();
227                    i18nParams.add(String.valueOf(entry.getValue()));
228                    getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_CONTENT_TYPE", i18nParams)));
229                }
230                
231                fillTableForContentType(contentTypeId);
232            }
233            
234            int pourcent = nbTypeContenu * 100 / nbTotalTypeContenu;
235            if (pourcent >= pourcentMax)
236            {
237                if (isInfoEnabled)
238                {
239                    List<String> i18nParams = new ArrayList<>();
240                    i18nParams.add(String.valueOf(pourcentMax));
241                    getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_ANALYZE", i18nParams)));
242                }
243                pourcentMax += 10;
244            }
245            
246            nbTypeContenu++;
247            _sqlTablePrefix = _sqlPrefixConf;
248        }
249        
250        if (isInfoEnabled)
251        {
252            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_END")));
253        }
254    }
255    
256    /**
257     * Fill values from JCR request with specific content type
258     * @param contentTypeId the content type id
259     * @throws SQLException if a sql error occurred
260     * @throws AmetysRepositoryException if an ametys repository error occurred
261     */
262    protected void fillTableForContentType(String contentTypeId) throws SQLException, AmetysRepositoryException
263    {
264        String tableName = getTableName(contentTypeId);
265        
266        ContentTypeExpression typeE = new ContentTypeExpression(Operator.EQ, contentTypeId);
267        String xpath = ContentQueryHelper.getContentXPathQuery(typeE);
268        AmetysObjectIterable<Content> contents = _resolver.query(xpath);
269        
270        ContentType cType = _contentTypeExtensionPoint.getExtension(contentTypeId);
271
272        boolean isInfoEnabled = getLogger().isInfoEnabled();
273        
274        int nbTotalContent = (int) contents.getSize();
275        if (isInfoEnabled && nbTotalContent != 0)
276        {
277            List<String> i18nParams = new ArrayList<>();
278            i18nParams.add(String.valueOf(nbTotalContent));
279            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_NB_CONTENT_MAX", i18nParams)));
280        }
281        
282        int nbContent = 0;
283        int pourcentMax = 10;
284        for (Content content : contents)
285        {
286            List<String> labels = Arrays.asList(((VersionableAmetysObject) content).getAllLabels());
287            boolean exportContent = true;
288            if (_exportOnlyValidatedContent)
289            {
290                if (labels.contains("Live"))
291                {
292                    ((VersionableAmetysObject) content).switchToLabel("Live");
293                }
294                else
295                {
296                    exportContent = false;
297                }
298            }
299            
300            if (exportContent)
301            {
302                String id = content.getId();
303                PreparedStatement stmt = null;
304                try 
305                {
306                    stmt = getInsertPreparedStatementFromTableName(_sqlTablePrefix, tableName, _mappingPolicy);
307                    stmt.setString(1, id);
308                    
309                    getLogger().debug(" with id: {}", id);
310                    
311                    ExportCounter fillIndex = new ExportCounter(2);
312                    fillValues(fillIndex, cType, content.getDataHolder(), tableName, "", id, stmt);
313                    fillAdditionalData(fillIndex, content, stmt);
314                    stmt.execute();
315                }
316                finally
317                {
318                    ConnectionHelper.cleanup(stmt);
319                }
320                
321                _fillContentTable(id, tableName);
322                executeInsert();
323                
324                int pourcent = nbContent * 100 / nbTotalContent;
325                if (pourcent >= pourcentMax)
326                {
327                    if (isInfoEnabled)
328                    {
329                        List<String> i18nParams = new ArrayList<>();
330                        i18nParams.add(String.valueOf(pourcentMax));
331                        getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_NB_CONTENT_FILL", i18nParams)));
332                    }
333                    pourcentMax += 10;
334                }
335                
336                nbContent++;
337            }
338        }
339        
340        if (isInfoEnabled && nbTotalContent != 0)
341        {
342            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_FILL_NB_CONTENT_FILL_MAX")));
343        }
344    }
345    
346    private void _fillContentTable(String idContent, String tableName) throws SQLException
347    {
348        String dataTableName = _sqlPrefixConf + ExportManager.CONTENT_TABLE_NAME;
349        PreparedStatement stmt = getInsertPreparedStatementFromTableName(_sqlPrefixConf, dataTableName, "FULL");
350        _stmtList.add(stmt);
351        
352        stmt.setString(1, idContent);
353        stmt.setString(2, tableName);
354
355        stmt.addBatch();
356        
357        if (getLogger().isDebugEnabled())
358        {
359            getLogger().debug(stmt.toString());
360        }
361    }
362    
363    /**
364     * Fill values from a composite metadata
365     * @param fillIndex the position indicator in the insert statement 
366     * @param modelItemContainer the attribute definitions
367     * @param dataHolder the {@link ModelAwareDataHolder}
368     * @param tableName the table name
369     * @param columnNamePrefix the column name prefix
370     * @param id the content id
371     * @param stmt the jdbc statement
372     * @throws SQLException if a sql error occurred
373     * @throws AmetysRepositoryException if an ametys repository error occurred
374     */
375    protected void fillValues(ExportCounter fillIndex, ModelItemContainer modelItemContainer, ModelAwareDataHolder dataHolder, String tableName, String columnNamePrefix, String id, PreparedStatement stmt) throws SQLException, AmetysRepositoryException
376    {
377        for (ModelItem modelItem : modelItemContainer.getModelItems())
378        {
379            String name = modelItem.getName();
380            String columnName = columnNamePrefix + name;
381            if (modelItem instanceof ElementDefinition)
382            {
383                // simple element
384                ElementDefinition definition = (ElementDefinition) modelItem;
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().getTime());
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().getTime());
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().getTime());
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().getTime());
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}