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