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.component;
017
018import java.io.IOException;
019import java.sql.Connection;
020import java.sql.PreparedStatement;
021import java.sql.SQLException;
022import java.util.ArrayList;
023import java.util.HashMap;
024import java.util.LinkedHashMap;
025import java.util.List;
026import java.util.Map;
027import java.util.Map.Entry;
028import java.util.regex.Matcher;
029import java.util.regex.Pattern;
030
031import org.apache.avalon.framework.component.Component;
032import org.apache.avalon.framework.context.ContextException;
033import org.apache.avalon.framework.service.ServiceException;
034import org.apache.avalon.framework.service.ServiceManager;
035import org.apache.avalon.framework.service.Serviceable;
036import org.apache.commons.lang.StringUtils;
037
038import org.ametys.cms.contenttype.ContentType;
039import org.ametys.cms.contenttype.ContentTypeExtensionPoint;
040import org.ametys.cms.contenttype.MetadataDefinition;
041import org.ametys.cms.contenttype.MetadataDefinitionHolder;
042import org.ametys.cms.contenttype.MetadataType;
043import org.ametys.cms.contenttype.RepeaterDefinition;
044import org.ametys.cms.languages.LanguagesManager;
045import org.ametys.core.datasource.ConnectionHelper;
046import org.ametys.core.util.I18nUtils;
047import org.ametys.plugins.contentio.export.ExportManager;
048import org.ametys.plugins.contentio.export.object.ExportConfiguration;
049import org.ametys.plugins.contentio.export.object.ExportTableInfo;
050import org.ametys.runtime.config.Config;
051import org.ametys.runtime.i18n.I18nizableText;
052import org.ametys.runtime.plugin.component.AbstractLogEnabled;
053
054/**
055 *  Create SQl Table Component
056 */
057public class CreateSqlTableComponent extends AbstractLogEnabled implements Component, Serviceable
058{
059    /** The component role */
060    public static final String ROLE = CreateSqlTableComponent.class.getName();
061    
062    /** Code of default language for comments */
063    public static final String DEFAULT_LANGUAGE_CODE_FOR_COMMENTS = "fr";
064    
065    /** The engine */
066    public static final String MYSQL_CONTENT_EXPORT_ENGINE = "MyISAM";
067    
068    /** The encoding */
069    public static final String MYSQL_CONTENT_EXPORT_CHARSET = "UTF8MB4";
070    
071    /** Prefix for parent table column */
072    public static final String COLUMN_PARENT_TABLE_PREFIX = "PID_";
073    
074    private static final Pattern _MYSQL_VERSION_NUMBER_EXTRACT = Pattern.compile("^([0-9]+).*$");
075    
076    /** Content type extension point. */
077    protected ContentTypeExtensionPoint _contentTypeExtensionPoint;
078    
079    /** The i18n translator. */
080    protected I18nUtils _i18nTranslator;
081    
082    /** The normalise name component. */
083    protected NormalizeNameComponent _normalizeNameComponent;
084    
085    /** The language manager */
086    protected LanguagesManager _languageManager;
087    
088    private Connection _connection;
089    private String _databaseType;
090    
091    private LinkedHashMap<String, ExportTableInfo> _tablesInfos;
092    private int _commentTableMaxLength;
093    private int _commentColumnMaxLength;
094    private String _sqlTablePrefix;
095    private String _sqlPrefixConf;
096    private Map<String, Map<String, String>> _mappingSql;
097    private Map<String, Map<String, String>> _reservedWords;
098    private String _mappingPolicy;
099    private ArrayList<String> _mappingTablesQueries;
100    private boolean _exportNoMultiValuedTable;
101    
102    private int _fkIndice;
103    private int _pkIndice;
104    
105    @Override
106    public void service(ServiceManager manager) throws ServiceException
107    {
108        _i18nTranslator = (I18nUtils) manager.lookup(I18nUtils.ROLE);
109        _contentTypeExtensionPoint = (ContentTypeExtensionPoint) manager.lookup(ContentTypeExtensionPoint.ROLE);
110        _normalizeNameComponent = (NormalizeNameComponent) manager.lookup(NormalizeNameComponent.ROLE);
111        _languageManager = (LanguagesManager) manager.lookup(LanguagesManager.ROLE);
112    } 
113    
114    /**
115     * Create sql tables for contents
116     * @param exportConfiguration the content export configuration
117     * @return tablesInfos
118     * @throws SQLException if a sql error occurred
119     * @throws ContextException if a context error occurred 
120     * @throws IOException if an IO error occurred
121     */
122    public synchronized Map<String, ExportTableInfo> createSqlTables(ExportConfiguration exportConfiguration) throws SQLException, ContextException, IOException
123    {
124        // Get from configuration
125        _sqlPrefixConf = exportConfiguration.getTablePrefix();
126        _sqlTablePrefix = exportConfiguration.getTablePrefix();
127        _mappingSql = exportConfiguration.getMappingSql();
128        _mappingPolicy = exportConfiguration.getMappingPolicy();
129        _reservedWords = exportConfiguration.getReservedWords();
130        _exportNoMultiValuedTable = exportConfiguration.exportNoMultiValuedTable();
131        
132        // Initialization
133        _fkIndice = 1;
134        _pkIndice = 1;
135        _tablesInfos = new LinkedHashMap<>();
136        _mappingTablesQueries = new ArrayList<>();
137        
138        try
139        {
140            String datasourceId = Config.getInstance().getValue("org.ametys.plugins.contentio.content.export.datasource");
141            _connection = ConnectionHelper.getConnection(datasourceId); 
142            _databaseType = ConnectionHelper.getDatabaseType(_connection);
143            
144            String productVersion = _connection.getMetaData().getDatabaseProductVersion();
145            initialize(productVersion);
146            
147            boolean isInfoEnabled = getLogger().isInfoEnabled();
148            if (isInfoEnabled)
149            {
150                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_BEGIN")));
151            }
152            
153            createTablesInfos(exportConfiguration.getContentTypesToExport());
154            
155            if (isInfoEnabled)
156            {
157                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_END")));
158                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_BEGIN")));
159            }
160            
161            createMappingNameTableSql();
162            
163            if (isInfoEnabled)
164            {
165                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_END")));
166            }
167            
168            createRichTextDataTable();
169            createContentTable();
170            
171            executeSqlQueries();
172        }
173        finally
174        {
175            ConnectionHelper.cleanup(_connection);
176        }
177        
178        return _tablesInfos;
179    }
180
181    /**
182     * Initialization
183     * @param productVersion The database product version
184     */
185    protected void initialize(String productVersion)
186    {
187        // Get the maximun number of authorized characters for table and column comments
188        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
189        {
190            String[] parts = productVersion.split("\\.");
191            
192            int majorVersion = Integer.parseInt(parts[0]);
193            int minorVersion = 0;
194            int patchVersion = 0;
195            if (parts.length > 1)
196            {
197                Matcher matcher = _MYSQL_VERSION_NUMBER_EXTRACT.matcher(parts[1]);
198                if (matcher.matches())
199                {
200                    minorVersion = Integer.parseInt(matcher.group(1));
201                }
202            }
203            
204            if (parts.length > 2)
205            {
206                Matcher matcher = _MYSQL_VERSION_NUMBER_EXTRACT.matcher(parts[2]);
207                if (matcher.matches())
208                {
209                    patchVersion = Integer.parseInt(matcher.group(1));
210                }
211            }
212            
213            if (majorVersion > 5 || (majorVersion >= 5 && minorVersion > 5) || (majorVersion >= 5 && minorVersion >= 5 && patchVersion >= 3))
214            {
215                // Version 5.5.3 or later
216                _commentTableMaxLength = 2048;
217                _commentColumnMaxLength = 1024;
218            }
219            else
220            {
221                // Version before 5.5.3
222                _commentTableMaxLength = 60;
223                _commentColumnMaxLength = 255;
224            }
225        }
226        else
227        {
228            // No max
229            _commentTableMaxLength = 2048;
230            _commentColumnMaxLength = 1024;
231        }
232    }
233    
234    /**
235     * Created all tables informations
236     * @param contents to export
237     * @throws SQLException if a sql error occurred
238     * @throws ContextException if a context error occurred 
239     */
240    protected void createTablesInfos(Map<String, String> contents) throws SQLException, ContextException
241    {
242        for (Entry<String, String> entry : contents.entrySet()) 
243        {
244            String contentTypeId = entry.getKey();
245
246            ContentType contentType = _contentTypeExtensionPoint.getExtension(contentTypeId);
247            if (!contentType.isAbstract())
248            {
249                String tableName = entry.getValue();
250                String comment = _i18nTranslator.translate(contentType.getLabel(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS) + ": " + _i18nTranslator.translate(contentType.getDescription(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
251                createSqlQueriesForTableCreation(contentType, tableName, null, comment, false);
252            }
253            _sqlTablePrefix = _sqlPrefixConf;
254        }
255            
256    }
257    
258    /**
259     * Create SQL queries to create necessary SQL tables for content type export
260     * @param metadataDef the metadata definition model The component holding metadata definitions
261     * @param tableName the table name The SQL table name
262     * @param tableParentName  The SQL parent table name
263     * @param comment the comment
264     * @param isSortTable true if table's rows have to be ordered
265     * @throws ContextException if a context error occurred 
266     */
267    protected void createSqlQueriesForTableCreation(MetadataDefinitionHolder metadataDef, String tableName, String tableParentName, String comment, boolean isSortTable) throws ContextException
268    {
269        ExportTableInfo tableInfo = new ExportTableInfo(tableName);
270        tableInfo.incrementNbColumns();
271        
272        String tableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
273        _tablesInfos.put(tableName, tableInfo);
274        
275        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
276        
277        currentCreateTableSQLQuery.append("CREATE TABLE ");
278        currentCreateTableSQLQuery.append(tableNameNormalized);
279        currentCreateTableSQLQuery.append(" (");
280        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, "id_" + tableName, tableName, _reservedWords, _connection));
281        currentCreateTableSQLQuery.append(" VARCHAR(250)");
282        currentCreateTableSQLQuery.append(createPrimaryKeyQuery());
283        currentCreateTableSQLQuery.append(createCommentQuery(tableName, "id_" + tableName, "Ametys ID"));
284        
285        if (StringUtils.isNotEmpty(tableParentName))
286        {
287            currentCreateTableSQLQuery.append(", ");
288            addColumnParentId(currentCreateTableSQLQuery, tableParentName, tableName);
289        }
290        
291        if (isSortTable)
292        {
293            currentCreateTableSQLQuery.append(", ");
294            addSortColumn(currentCreateTableSQLQuery, tableName);
295        }
296        
297        addColumnForCompositeMetadata(metadataDef, currentCreateTableSQLQuery, tableName, "");
298        addAdditionnalContentMetadata(metadataDef, currentCreateTableSQLQuery, tableName);
299        
300        currentCreateTableSQLQuery.append(") ");
301        currentCreateTableSQLQuery.append(createEngineQuery());
302        currentCreateTableSQLQuery.append(createCommentQuery(tableName, null, comment));
303        
304        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
305    }  
306    
307    /**
308     * Create table for a multiple metadata
309     * @param metadataDef the metadata definition model
310     * @param tableParentName the table parent name
311     * @param tableName the table name
312     * @throws ContextException if a context error occurred 
313     */
314    protected void createSqlQueryForMultipleMetadata(MetadataDefinition metadataDef, String tableParentName, String tableName) throws ContextException
315    {
316        ExportTableInfo tableInfo = new ExportTableInfo(tableName);
317        tableInfo.incrementNbColumns();
318        
319        String normalizeTableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
320        _tablesInfos.put(tableName, tableInfo);
321        
322        String normalizedColumnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, COLUMN_PARENT_TABLE_PREFIX + tableParentName, tableName, _reservedWords, _connection);
323        
324        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
325        currentCreateTableSQLQuery.append("CREATE TABLE ");
326        currentCreateTableSQLQuery.append(normalizeTableName);
327        currentCreateTableSQLQuery.append(" (");
328        currentCreateTableSQLQuery.append(normalizedColumnName);
329        currentCreateTableSQLQuery.append(" VARCHAR(245)");
330        currentCreateTableSQLQuery.append(createCommentQuery(tableName, COLUMN_PARENT_TABLE_PREFIX + tableParentName, "Parent ID of the multiple metadata"));
331        
332        String fkName = normalizedColumnName;
333        String fkTableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableParentName, _connection);
334        String fkColumnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, "id_" + tableParentName, tableParentName, _reservedWords, _connection);
335        currentCreateTableSQLQuery.append(createForeignKeyQuery(fkName, fkTableName, fkColumnName));
336        
337        addColumnForSingleMetadata(metadataDef, currentCreateTableSQLQuery, tableName, metadataDef.getName());
338        
339        currentCreateTableSQLQuery.append(", ");
340        addSortColumn(currentCreateTableSQLQuery, tableName);
341        
342        String primaryKey = "pk_" + _pkIndice;
343        _pkIndice++;
344        
345        currentCreateTableSQLQuery.append(", CONSTRAINT ");
346        currentCreateTableSQLQuery.append(primaryKey);
347        currentCreateTableSQLQuery.append(" PRIMARY KEY (");
348        currentCreateTableSQLQuery.append(normalizedColumnName);
349        currentCreateTableSQLQuery.append(", position)");
350        currentCreateTableSQLQuery.append(") ");
351        currentCreateTableSQLQuery.append(createEngineQuery());
352        
353        String comment = "Multiple metadata " + metadataDef.getName() + " linked to the table" + _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableParentName, _connection);
354        currentCreateTableSQLQuery.append(createCommentQuery(tableName, null, comment));
355        
356        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
357    }
358    
359    /**
360     * Create table for images in rich text
361     * @throws ContextException if a context error occurred 
362     */
363    protected void createRichTextDataTable() throws ContextException
364    {
365        String dataTableName = _sqlTablePrefix + ExportManager.RICH_TEXT_DATA_TABLE_NAME;
366        ExportTableInfo tableInfo = new ExportTableInfo(dataTableName);
367        tableInfo.incrementNbColumns(8);
368
369        String dateTableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", dataTableName, _connection);
370        _tablesInfos.put(dataTableName, tableInfo);
371        
372        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
373
374        currentCreateTableSQLQuery.append("CREATE TABLE ");
375        currentCreateTableSQLQuery.append(dateTableNameNormalized);
376        currentCreateTableSQLQuery.append(" (id_data VARCHAR(250)");
377        currentCreateTableSQLQuery.append(createPrimaryKeyQuery());
378        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "id_data", "Data ID"));
379        currentCreateTableSQLQuery.append(", id_content VARCHAR(255)");
380        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "id_content", "Parent ID"));
381        
382        currentCreateTableSQLQuery.append(", metadata_name ");
383        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("string"));
384        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "metadata_name", "Richtext metadata name"));
385        currentCreateTableSQLQuery.append(", data_name ");
386        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("string"));
387        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_name", "Name"));
388        
389        currentCreateTableSQLQuery.append(", data ");
390        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("file"));
391        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data", "Data"));
392        currentCreateTableSQLQuery.append(", data_mimetype VARCHAR(255)");
393        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_mimetype", "Mime type"));
394        currentCreateTableSQLQuery.append(", data_size INT");
395        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_size", "Size"));
396        currentCreateTableSQLQuery.append(", data_lastmodified ");
397        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("datetime"));
398        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_lastmodified", "Last modification date"));
399        currentCreateTableSQLQuery.append(", ");
400        
401        addSortColumn(currentCreateTableSQLQuery, dataTableName);
402        
403        currentCreateTableSQLQuery.append(") ");
404        currentCreateTableSQLQuery.append(createEngineQuery());
405
406        String comment = "Data table of all rich text";
407        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, null, comment));
408        
409        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
410    }
411    
412    /**
413     * Create table for metadata content
414     * @throws ContextException if a context error occurred 
415     */
416    protected void createContentTable() throws ContextException
417    {
418        String dataTableName = _sqlPrefixConf + ExportManager.CONTENT_TABLE_NAME;
419        ExportTableInfo tableInfo = new ExportTableInfo(dataTableName);
420        tableInfo.incrementNbColumns(2);
421
422        String dataTableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", dataTableName, _connection);
423        _tablesInfos.put(dataTableName, tableInfo);
424        
425        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
426
427        currentCreateTableSQLQuery.append("CREATE TABLE ");
428        currentCreateTableSQLQuery.append(dataTableNameNormalized);
429        currentCreateTableSQLQuery.append(" (id_content VARCHAR(80)");
430        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "id_content", "Content ID"));
431        currentCreateTableSQLQuery.append(", table_name VARCHAR(170)");
432        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "table_name", "Table name"));
433        currentCreateTableSQLQuery.append(", CONSTRAINT pk_content PRIMARY KEY (id_content, table_name) ");
434        currentCreateTableSQLQuery.append(") ");
435        currentCreateTableSQLQuery.append(createEngineQuery());
436
437        String comment = "Link table of content with it's own table";
438        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, null, comment));
439        
440        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
441    }
442    
443    /**
444     * Create the table for the enumerator (key, value)
445     * @param metadataDef the metadata definition model
446     * @param tableParentName the table parent name
447     * @param tableName the table name
448     * @param columnName the column name
449     * @param currentCreateTableSQLQuery the current SQL create table query 
450     * @throws ContextException if a context error occurred 
451     */
452    protected void createTableForEnumerator(MetadataDefinition metadataDef, String tableParentName, String tableName, String columnName, StringBuilder currentCreateTableSQLQuery) throws ContextException
453    {
454        ExportTableInfo tableInfo = new ExportTableInfo(tableName);
455        tableInfo.incrementNbColumns();
456        
457        String tableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
458        _tablesInfos.put(tableName, tableInfo);
459        
460        StringBuilder sql = new StringBuilder();
461        
462        sql.append("CREATE TABLE ");
463        sql.append(tableNameNormalized);
464        sql.append(" (key_enum ");
465        sql.append(metadataDef.getType().equals(MetadataType.LONG) ? "INT " : "VARCHAR(250) ");
466        sql.append(createPrimaryKeyQuery());
467        sql.append(createCommentQuery(tableName, "key_enum_" + metadataDef.getName(), "Enumerator key"));
468        
469        sql.append(", ");
470        addColumnForStringMetadata(sql, "value_enum_" + metadataDef.getName(), tableName);
471        
472        sql.append(") ");
473        sql.append(createEngineQuery());
474        sql.append(createCommentQuery(tableName, null, "Enumerator table " + metadataDef.getName() + " linked to the table " + tableParentName));
475        
476        tableInfo.addCreateQuery(sql.toString());
477        
478//        String fkName = _normaliseNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableParentName, _connection);
479//        String fkTableName = _normaliseNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
480//        String fkColumnName = "key_enum";
481//        currentCreateTableSQLQuery.append(createForeignKeyQuery(fkName, fkTableName, fkColumnName));
482        //TODO foreign key ??
483        
484        fillTableForEnumerator(metadataDef, tableName);
485    }
486    
487    /**
488     * Fill values for the enumerator table
489     * @param metadataDef the metadata definition model
490     * @param tableName the table name
491     */
492    protected void fillTableForEnumerator(MetadataDefinition metadataDef, String tableName)
493    {   
494        try
495        {
496            for (Entry<Object, I18nizableText> entry : metadataDef.getEnumerator().getEntries().entrySet()) 
497            {
498                String enumValue = _i18nTranslator.translate(entry.getValue(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
499                String enumKey = (String) entry.getKey();
500
501                StringBuilder sql = new StringBuilder();
502                sql.append("INSERT INTO ");
503                sql.append(_normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection));
504                sql.append(" VALUES ( '");
505                sql.append(_normalizeNameComponent.escapeValue(enumKey, _connection));
506                sql.append("', '");
507                sql.append(_normalizeNameComponent.escapeValue(enumValue, _connection));
508                sql.append("')");
509
510                _tablesInfos.get(tableName).addInsertQuery(sql.toString());
511            }
512        }
513        catch (IllegalStateException e)
514        {
515            getLogger().warn(e.getMessage(), e);
516        }
517        catch (Exception e)
518        {
519            getLogger().warn(e.getMessage(), e);
520        }
521    }
522    
523    /**
524     * Create the two table mapping (for table name and column name)
525     * @throws ContextException if a context error occurred
526     */
527    protected void createMappingNameTableSql() throws ContextException
528    {
529        PreparedStatement stmt = null;
530        String mappingTableName = _sqlTablePrefix + ExportManager.MAPPING_TABLE_NAME;
531        String mappingColumnName = _sqlTablePrefix + ExportManager.MAPPING_COLUMN_NAME;
532        
533        ExportTableInfo mappingTableInfo = new ExportTableInfo(mappingTableName);
534        mappingTableInfo.incrementNbColumns(3);
535
536        ExportTableInfo mappingColumnInfo = new ExportTableInfo(mappingColumnName);
537        mappingColumnInfo.incrementNbColumns(3);
538
539        String mappingTableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", mappingTableName, _connection);
540        String mappingColumnNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", mappingColumnName, _connection);
541        
542        _tablesInfos.put(mappingTableName, mappingTableInfo);
543        _tablesInfos.put(mappingColumnName, mappingColumnInfo);
544        
545        StringBuilder createMappingTableSQLQuery = new StringBuilder();
546
547        createMappingTableSQLQuery.append("CREATE TABLE ");
548        createMappingTableSQLQuery.append(mappingTableNameNormalized);
549        createMappingTableSQLQuery.append(" (id_table INT");
550        createMappingTableSQLQuery.append(createPrimaryKeyQuery());
551        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, "id_table", "Table name ID"));
552        createMappingTableSQLQuery.append(", real_name VARCHAR(512)");
553        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, "real_name", "Real name"));
554        createMappingTableSQLQuery.append(", modified_name VARCHAR(512)");
555        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, "modified_name", "Normalized name"));
556        
557        createMappingTableSQLQuery.append(") ");
558        createMappingTableSQLQuery.append(createEngineQuery());
559
560        String comment = "Mapping table between real name and normalized name.";
561        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, null, comment));
562        
563        try
564        {
565            _mappingTablesQueries.add(createMappingTableSQLQuery.toString());
566            stmt = _connection.prepareStatement(createMappingTableSQLQuery.toString());
567            stmt.execute();
568        }
569        catch (SQLException e)
570        {
571            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_TABLE_ERROR")), e);
572        }
573        finally
574        {
575            ConnectionHelper.cleanup(stmt);
576        }
577        
578        StringBuilder createMappingColumnSQLQuery = new StringBuilder();
579
580        createMappingColumnSQLQuery.append("CREATE TABLE ");
581        createMappingColumnSQLQuery.append(mappingColumnNameNormalized);
582        createMappingColumnSQLQuery.append(" (id_table INT");
583        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, "id_table", "Table name id"));
584        createMappingColumnSQLQuery.append(createForeignKeyQuery("id_table", _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, mappingTableName, _connection), "id_table"));
585        createMappingColumnSQLQuery.append(", real_name VARCHAR(512)");
586        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, "real_name", "Real column name"));
587        createMappingColumnSQLQuery.append(", modified_name VARCHAR(512)");
588        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, "modified_name", "Normalized column name"));
589        
590        createMappingColumnSQLQuery.append(") ");
591        createMappingColumnSQLQuery.append(createEngineQuery());
592
593        String commentColum = "Mapping table between real column name and normalized column name.";
594        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, null, commentColum));
595        
596        try
597        {
598            _mappingTablesQueries.add(createMappingColumnSQLQuery.toString());
599            stmt = _connection.prepareStatement(createMappingColumnSQLQuery.toString());
600            stmt.execute();
601        }
602        catch (SQLException e)
603        {
604            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_COLUMN_ERROR")), e);
605        }
606        finally
607        {
608            ConnectionHelper.cleanup(stmt);
609        }
610        
611        fillMappingTableSql(mappingTableName, mappingColumnName);
612    }
613    
614    /**
615     * Fill the two mapping table (for table name and column name)
616     * @param mappingTableName the table mapping table name
617     * @param mappingColumnName the column mapping table name
618     * @throws ContextException if a context error occurred
619     */
620    protected void fillMappingTableSql(String mappingTableName, String mappingColumnName) throws ContextException 
621    {
622        HashMap<String, String> mappingTable = (HashMap<String, String>) _normalizeNameComponent.getMappingTableNameFromCache();
623        HashMap<String, HashMap<String, String>> mappingColumn = (HashMap<String, HashMap<String, String>>) _normalizeNameComponent.getMappingTableColumnNameFromCache();
624        
625        PreparedStatement stmtTableName = null;
626        PreparedStatement stmtColumnName = null;
627        try
628        {
629            stmtTableName = getInsertPreparedStatementFromTableName(mappingTableName);
630            stmtColumnName = getInsertPreparedStatementFromTableName(mappingColumnName);
631            int i = 0;
632            for (Entry<String, String> entry : mappingTable.entrySet()) 
633            {
634                String realName = entry.getKey();
635                String modifiedName = entry.getValue();
636                
637                if (getLogger().isDebugEnabled())
638                {
639                    String tableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, mappingTableName, _connection);
640                    getLogger().debug("INSERT INTO {} VALUES ('{}', '{}', '{}')", tableName, i, realName, modifiedName);
641                }
642                
643                stmtTableName.setInt(1, i);
644                stmtTableName.setString(2, realName);
645                stmtTableName.setString(3, modifiedName);
646                
647                stmtTableName.addBatch();
648                
649                if (mappingColumn.containsKey(realName))
650                {
651                    for (Entry<String, String> entryCol : mappingColumn.get(realName).entrySet())
652                    {
653                        String realNameCol = entryCol.getKey();
654                        String modifiedNameCol = entryCol.getValue();
655                        
656                        if (getLogger().isDebugEnabled())
657                        {
658                            String tableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, mappingTableName, _connection);
659                            getLogger().debug("INSERT INTO {} VALUES ('{}', '{}', '{}')", tableName, i, realNameCol, modifiedNameCol);
660                        }
661
662                        stmtColumnName.setInt(1, i);
663                        stmtColumnName.setString(2, realNameCol);
664                        stmtColumnName.setString(3, modifiedNameCol);
665                        
666                        stmtColumnName.addBatch();
667                        
668                    }
669                }
670                i++;
671            }
672            
673            stmtTableName.executeBatch();
674            stmtColumnName.executeBatch();
675        }
676        catch (SQLException e)
677        {
678            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_FILL_MAPPING_ERROR")), e);
679        }
680        finally
681        {
682            ConnectionHelper.cleanup(stmtTableName);
683            ConnectionHelper.cleanup(stmtColumnName);
684        }
685    }
686    
687    /**
688     * Add all the column for each metadata in the composite
689     * @param metadataDef the metadata definition model
690     * @param currentCreateTableSQLQuery the current SQL create table query
691     * @param tableName the table name
692     * @param columnNamePrefix the column namePrefix
693     * @throws ContextException if a context error occurred 
694     */
695    protected void addColumnForCompositeMetadata(MetadataDefinitionHolder metadataDef, StringBuilder currentCreateTableSQLQuery, String tableName, String columnNamePrefix) throws ContextException
696    {
697        for (String metadataName : metadataDef.getMetadataNames())
698        {
699            MetadataDefinition subMetadataDef = metadataDef.getMetadataDefinition(metadataName);
700            addColumnForMetadata(subMetadataDef, currentCreateTableSQLQuery, tableName, columnNamePrefix);
701        }
702    }
703    
704    /**
705     * Add column for the metadata (multiple or single)
706     * @param metadataDef the metadata definition model
707     * @param currentCreateTableSQLQuery the current SQL create table query
708     * @param tableName the table name
709     * @param columnNamePrefix the column namePrefix
710     * @throws ContextException if a context error occurred 
711     */
712    protected void addColumnForMetadata(MetadataDefinition metadataDef, StringBuilder currentCreateTableSQLQuery, String tableName, String columnNamePrefix) throws ContextException
713    {
714        String columnName = columnNamePrefix + metadataDef.getName();
715        
716        if (metadataDef.isMultiple())
717        {
718            if (_exportNoMultiValuedTable)
719            {
720                String comment = _i18nTranslator.translate(metadataDef.getLabel(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS) + ": " + _i18nTranslator.translate(metadataDef.getDescription(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
721
722                currentCreateTableSQLQuery.append(", ");
723                addColumnForStringMetadata(currentCreateTableSQLQuery, columnName, tableName);
724                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
725            }
726            else
727            {
728                createSqlQueryForMultipleMetadata(metadataDef, tableName, tableName + "_" + columnName); 
729            }
730        }
731        else
732        {
733            addColumnForSingleMetadata(metadataDef, currentCreateTableSQLQuery, tableName, columnName);
734        }
735    }
736   
737    /**
738     * Add column for single metadata
739     * @param metadataDef the metadata definition model
740     * @param currentCreateTableSQLQuery the current SQL create table query
741     * @param tableName the table name
742     * @param columnName the column name
743     * @throws ContextException if a context error occurred 
744     */
745    protected void addColumnForSingleMetadata(MetadataDefinition metadataDef, StringBuilder currentCreateTableSQLQuery, String tableName, String columnName) throws ContextException
746    {
747        String comment = _i18nTranslator.translate(metadataDef.getLabel(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS) + ": " + _i18nTranslator.translate(metadataDef.getDescription(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
748
749        MetadataType metadataType = metadataDef.getType();
750        switch (metadataType)
751        {
752            case USER:
753                currentCreateTableSQLQuery.append(", ");
754                addColumnForUserMetadata(currentCreateTableSQLQuery, columnName, tableName);
755                break;
756            case STRING:
757                currentCreateTableSQLQuery.append(", ");
758                if (metadataDef.getEnumerator() != null)
759                {
760                    addColumnForEnumStringMetadata(currentCreateTableSQLQuery, columnName, tableName);
761                }
762                else
763                {
764                    addColumnForStringMetadata(currentCreateTableSQLQuery, columnName, tableName);
765                }
766                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
767                break;
768            case MULTILINGUAL_STRING:
769                currentCreateTableSQLQuery.append(", ");
770                addColumnsForMultilingualStringMetadata(currentCreateTableSQLQuery, columnName, tableName, comment);
771                break;
772            case LONG:
773                currentCreateTableSQLQuery.append(", ");
774                addColumnForLongMetadata(currentCreateTableSQLQuery, columnName, tableName);
775                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
776                break;
777            case BOOLEAN:
778                currentCreateTableSQLQuery.append(", ");
779                addColumnForBooleanMetadata(currentCreateTableSQLQuery, columnName, tableName);
780                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
781                break;
782            case DATE:
783                currentCreateTableSQLQuery.append(", ");
784                addColumnForDateMetadata(currentCreateTableSQLQuery, columnName, tableName);
785                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
786                break;
787            case DATETIME:
788                currentCreateTableSQLQuery.append(", ");
789                addColumnForDateTimeMetadata(currentCreateTableSQLQuery, columnName, tableName);
790                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
791                break;
792            case DOUBLE:
793                currentCreateTableSQLQuery.append(", ");
794                addColumnForDoubleMetadata(currentCreateTableSQLQuery, columnName, tableName);
795                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
796                break;
797            case RICH_TEXT:
798                currentCreateTableSQLQuery.append(", ");
799                addColumnForRichTextMetadata(currentCreateTableSQLQuery, columnName, tableName);
800                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
801                break;
802            case CONTENT:
803                currentCreateTableSQLQuery.append(", ");
804                addColumnForContentMetadata(currentCreateTableSQLQuery, columnName, tableName);
805                currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
806                break;
807            case FILE:
808                currentCreateTableSQLQuery.append(", ");
809                addColumnsForFileMetadata(currentCreateTableSQLQuery, columnName, tableName, comment);
810                break;
811            case BINARY:
812                currentCreateTableSQLQuery.append(", ");
813                addColumnsForBinaryMetadata(currentCreateTableSQLQuery, columnName, tableName, comment);
814                break;
815            case GEOCODE:
816                currentCreateTableSQLQuery.append(", ");
817                addColumnsForGeocodeMetadata(currentCreateTableSQLQuery, columnName, tableName, comment);
818                break;
819            case REFERENCE:
820            case COMPOSITE:
821                if (metadataDef instanceof RepeaterDefinition)
822                {
823                    String commentTable = "Repeater " + _normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection) + " linked to the table " + _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
824                    createSqlQueriesForTableCreation(metadataDef, tableName + "_" + columnName, tableName, commentTable, true);
825                }
826                else
827                {    
828                    addColumnForCompositeMetadata(metadataDef, currentCreateTableSQLQuery, tableName, columnName + "_");
829                }
830                break;
831            default:
832                break;
833        }
834        
835        if (metadataDef.getEnumerator() != null)
836        {        
837            createTableForEnumerator(metadataDef, tableName, tableName + "_" + metadataDef.getName(), columnName, currentCreateTableSQLQuery);
838        }
839    }
840
841    /**
842     * Add a column to sort
843     * @param currentCreateTableSQLQuery the current SQL create table query
844     * @param tableName the table name
845     * @throws ContextException if a context error occurred 
846     */
847    protected void addSortColumn(StringBuilder currentCreateTableSQLQuery, String tableName) throws ContextException
848    {
849        currentCreateTableSQLQuery.append("position INT");
850        currentCreateTableSQLQuery.append(createCommentQuery(tableName, "position", "Order of the row"));
851        
852        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
853        tableInfo.incrementNbColumns();
854    }
855    
856    /**
857     * Add an id column which refer to another table
858     * @param currentCreateTableSQLQuery the current SQL create table query
859     * @param tableParentName the table parent name
860     * @param tableName the table name
861     * @throws ContextException if a context error occurred 
862     */
863    protected void addColumnParentId(StringBuilder currentCreateTableSQLQuery, String tableParentName, String tableName) throws ContextException
864    {
865        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, COLUMN_PARENT_TABLE_PREFIX + tableParentName, tableName, _reservedWords, _connection));
866        currentCreateTableSQLQuery.append(" VARCHAR(250)"); //TODO NOT NULL
867        currentCreateTableSQLQuery.append(createCommentQuery(tableName, COLUMN_PARENT_TABLE_PREFIX + tableParentName, "Parent table ID " + tableParentName));
868
869        String fkName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, COLUMN_PARENT_TABLE_PREFIX + tableParentName, tableName, _reservedWords, _connection);
870        String fkTableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableParentName, _connection);
871        String fkColumnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, "id_" + tableParentName, tableParentName, _reservedWords, _connection);
872        currentCreateTableSQLQuery.append(createForeignKeyQuery(fkName, fkTableName, fkColumnName));
873        
874        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
875        tableInfo.incrementNbColumns();
876    }
877    
878    /**
879     * Add columns for geocode metadata
880     * @param currentCreateTableSQLQuery the current SQL create table query
881     * @param initialColumnName the initial column name
882     * @param tableName the table name
883     * @param comment the comment
884     * @throws ContextException if a context error occurred 
885     */
886    protected void addColumnsForGeocodeMetadata(StringBuilder currentCreateTableSQLQuery, String initialColumnName, String tableName, String comment) throws ContextException
887    {
888        String columnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, initialColumnName, tableName, _reservedWords, _connection);
889        
890        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_longitude", tableName, _reservedWords, _connection));
891        currentCreateTableSQLQuery.append(" ");
892        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("double"));
893        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Longitude of " + columnName));
894        currentCreateTableSQLQuery.append(", ");
895        
896        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_latitude", tableName, _reservedWords, _connection));
897        currentCreateTableSQLQuery.append(" ");
898        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("double"));
899        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Latitude type of " + columnName));
900        
901        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
902        tableInfo.incrementNbColumns(2);
903    }
904    
905    /**
906     * Add columns for binary metadata
907     * @param currentCreateTableSQLQuery the current SQL create table query
908     * @param initialColumnName the initial column name
909     * @param tableName the table name
910     * @param comment the comment
911     * @throws ContextException if a context error occurred 
912     */
913    protected void addColumnsForBinaryMetadata(StringBuilder currentCreateTableSQLQuery, String initialColumnName, String tableName, String comment) throws ContextException
914    {
915        String columnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, initialColumnName, tableName, _reservedWords, _connection);
916        
917        currentCreateTableSQLQuery.append(columnName);
918        currentCreateTableSQLQuery.append(" ");
919        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("string"));
920        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, comment));
921        currentCreateTableSQLQuery.append(", ");
922        
923        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_data", tableName, _reservedWords, _connection));
924        currentCreateTableSQLQuery.append(" ");
925        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("binary"));
926        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Data of " + columnName));
927        currentCreateTableSQLQuery.append(", ");
928        
929        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_mimetype", tableName, _reservedWords, _connection));
930        currentCreateTableSQLQuery.append(" VARCHAR(255)");
931        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Mime type of " + columnName));
932        currentCreateTableSQLQuery.append(", ");
933        
934        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_size", tableName, _reservedWords, _connection));
935        currentCreateTableSQLQuery.append(" INT");
936        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Size of " + columnName));
937        currentCreateTableSQLQuery.append(", ");
938        
939        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_lastmodified", tableName, _reservedWords, _connection));
940        currentCreateTableSQLQuery.append(" ");
941        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("datetime"));
942        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Last modification date of " + columnName));
943        
944        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
945        tableInfo.incrementNbColumns(5);
946    }
947    
948    /**
949     * Add columns for file metadata
950     * @param currentCreateTableSQLQuery the current SQL create table query
951     * @param initialColumnName the initial column name
952     * @param tableName the table name
953     * @param comment the comment
954     * @throws ContextException if a context error occurred 
955     */
956    protected void addColumnsForFileMetadata(StringBuilder currentCreateTableSQLQuery, String initialColumnName, String tableName, String comment) throws ContextException
957    {
958        String columnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, initialColumnName, tableName, _reservedWords, _connection);
959        
960        currentCreateTableSQLQuery.append(columnName);
961        currentCreateTableSQLQuery.append(" ");
962        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("string"));
963        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, comment));
964        currentCreateTableSQLQuery.append(", ");
965        
966        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_data", tableName, _reservedWords, _connection));
967        currentCreateTableSQLQuery.append(" ");
968        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("file"));
969        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Data of " + columnName));
970        currentCreateTableSQLQuery.append(", ");
971        
972        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_mimetype", tableName, _reservedWords, _connection));
973        currentCreateTableSQLQuery.append(" VARCHAR(255)");
974        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Mime type of " + columnName));
975        currentCreateTableSQLQuery.append(", ");
976        
977        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_size", tableName, _reservedWords, _connection));
978        currentCreateTableSQLQuery.append(" INT");
979        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Size of " + columnName));
980        currentCreateTableSQLQuery.append(", ");
981        
982        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_lastmodified", tableName, _reservedWords, _connection));
983        currentCreateTableSQLQuery.append(" ");
984        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("datetime"));
985        currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Last modification date of " + columnName));
986        
987        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
988        tableInfo.incrementNbColumns(5);
989    }
990
991    /**
992     * Add columns for multilangual string metadata (one for each language)
993     * @param currentCreateTableSQLQuery the current SQL create table query
994     * @param initialColumnName the initial column name
995     * @param tableName the table name
996     * @param comment the comment
997     * @throws ContextException if a context error occurred 
998     */
999    protected void addColumnsForMultilingualStringMetadata(StringBuilder currentCreateTableSQLQuery, String initialColumnName, String tableName, String comment) throws ContextException
1000    {
1001        String columnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, initialColumnName, tableName, _reservedWords, _connection);
1002        
1003        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1004        int position = 0;
1005        for (String lang : _languageManager.getAvailableLanguages().keySet())
1006        {
1007            if (position != 0)
1008            {
1009                currentCreateTableSQLQuery.append(", ");
1010            }
1011
1012            currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName + "_" + lang, tableName, _reservedWords, _connection));
1013            currentCreateTableSQLQuery.append(" ");
1014            currentCreateTableSQLQuery.append(convertMetadataTypeToSql("string"));
1015            currentCreateTableSQLQuery.append(createCommentQuery(tableName, initialColumnName, "Value of " + columnName + " for lang " + lang));
1016            
1017            position++;
1018            tableInfo.incrementNbColumns();
1019        }
1020    }
1021    
1022    /**
1023     * Add column for rich Text
1024     * @param currentCreateTableSQLQuery the current SQL create table query
1025     * @param columnName the column name
1026     * @param tableName the table name
1027     * @throws ContextException if a context error occurred 
1028     */
1029    protected void addColumnForRichTextMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1030    {
1031        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1032        currentCreateTableSQLQuery.append(" ");
1033        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("richtext"));
1034        
1035        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1036        tableInfo.incrementNbColumns();
1037    }
1038
1039    /**
1040     * Add column for content metadata
1041     * @param currentCreateTableSQLQuery the current SQL create table query
1042     * @param columnName the column name
1043     * @param tableName the table name
1044     * @throws ContextException if a context error occurred 
1045     */
1046    private void addColumnForContentMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1047    {
1048        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1049        currentCreateTableSQLQuery.append(" ");
1050        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("content"));
1051        
1052        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1053        tableInfo.incrementNbColumns();
1054    }
1055    
1056    /**
1057     * Add column for Double metadata
1058     * @param currentCreateTableSQLQuery the current SQL create table query
1059     * @param columnName the column name
1060     * @param tableName the table name
1061     * @throws ContextException if a context error occurred 
1062     */
1063    protected void addColumnForDoubleMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1064    {
1065        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1066        currentCreateTableSQLQuery.append(" ");
1067        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("double"));
1068        
1069        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1070        tableInfo.incrementNbColumns();
1071    }
1072    
1073    /**
1074     * Add column for DateTime metadata
1075     * @param currentCreateTableSQLQuery the current SQL create table query
1076     * @param columnName the column name
1077     * @param tableName the table name
1078     * @throws ContextException if a context error occurred 
1079     */
1080    protected void addColumnForDateTimeMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1081    {
1082        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1083        currentCreateTableSQLQuery.append(" ");
1084        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("datetime"));
1085        
1086        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1087        tableInfo.incrementNbColumns();
1088    }
1089
1090    /**
1091     * Add column for Date metadata
1092     * @param currentCreateTableSQLQuery the current SQL create table query
1093     * @param columnName the column name
1094     * @param tableName the table name
1095     * @throws ContextException if a context error occurred 
1096     */
1097    protected void addColumnForDateMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1098    {
1099        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1100        currentCreateTableSQLQuery.append(" ");
1101        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("date"));
1102        
1103        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1104        tableInfo.incrementNbColumns();
1105    }
1106
1107    /**
1108     * Add column for String metadata
1109     * @param currentCreateTableSQLQuery the current SQL create table query
1110     * @param columnName the column name
1111     * @param tableName the table name
1112     * @throws ContextException if a context error occurred 
1113     */
1114    protected void addColumnForStringMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1115    {
1116        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1117        currentCreateTableSQLQuery.append(" ");
1118        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("string"));
1119        
1120        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1121        tableInfo.incrementNbColumns();
1122    }
1123    
1124    /**
1125     * Add column for User metadata
1126     * @param currentCreateTableSQLQuery the current SQL create table query
1127     * @param columnName the column name
1128     * @param tableName the table name
1129     * @throws ContextException if a context error occurred 
1130     */
1131    protected void addColumnForUserMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1132    {
1133        addColumnForStringMetadata(currentCreateTableSQLQuery, columnName + "_login", tableName);
1134        currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName + "_login", "User login"));
1135        currentCreateTableSQLQuery.append(", ");
1136        addColumnForStringMetadata(currentCreateTableSQLQuery, columnName + "_population", tableName);
1137        currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName + "_population", "User population"));
1138    }
1139    
1140    /**
1141     * Add column for enumerator metadata
1142     * @param currentCreateTableSQLQuery the current SQL create table query
1143     * @param columnName the column name
1144     * @param tableName the table name
1145     * @throws ContextException if a context error occurred 
1146     */
1147    protected void addColumnForEnumStringMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1148    {
1149        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1150        currentCreateTableSQLQuery.append(" ");
1151        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("string"));
1152        
1153        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1154        tableInfo.incrementNbColumns();
1155    }
1156
1157    /**
1158     * Add column for Boolean metadata
1159     * @param currentCreateTableSQLQuery the current SQL create table query
1160     * @param columnName the column name
1161     * @param tableName the table name
1162     * @throws ContextException if a context error occurred 
1163     */
1164    protected void addColumnForBooleanMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1165    {
1166        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1167        currentCreateTableSQLQuery.append(" ");
1168        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("boolean"));
1169        
1170        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1171        tableInfo.incrementNbColumns();
1172    }
1173
1174    /**
1175     * Add column for Long metadata
1176     * @param currentCreateTableSQLQuery the current SQL create table query
1177     * @param columnName the column name
1178     * @param tableName the table name
1179     * @throws ContextException if a context error occurred 
1180     */
1181    protected void addColumnForLongMetadata(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName) throws ContextException
1182    {
1183        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection));
1184        currentCreateTableSQLQuery.append(" ");
1185        currentCreateTableSQLQuery.append(convertMetadataTypeToSql("long"));
1186        
1187        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1188        tableInfo.incrementNbColumns();
1189    }
1190    
1191    /**
1192     * Add additionnal metadata for content (title, type, language, creator, creationDate, ....)
1193     * @param metadataDef the metadata definition model
1194     * @param currentCreateTableSQLQuery the current SQL create table query
1195     * @param tableName the table name
1196     * @throws ContextException if a context error occurred 
1197     */
1198    protected void addAdditionnalContentMetadata(MetadataDefinitionHolder metadataDef, StringBuilder currentCreateTableSQLQuery, String tableName) throws ContextException
1199    {
1200        if (metadataDef instanceof ContentType)
1201        {
1202            currentCreateTableSQLQuery.append(", ");
1203            addColumnForStringMetadata(currentCreateTableSQLQuery, "content_title", tableName);
1204            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_title", "Content title"));
1205            currentCreateTableSQLQuery.append(", ");
1206            
1207            addColumnForStringMetadata(currentCreateTableSQLQuery, "content_type", tableName);
1208            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_type", "Content type"));
1209            currentCreateTableSQLQuery.append(", ");
1210            //TODO site for webContent
1211            addColumnForStringMetadata(currentCreateTableSQLQuery, "content_language", tableName);
1212            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_language", "Content lang"));
1213            currentCreateTableSQLQuery.append(", ");
1214            
1215            addColumnForStringMetadata(currentCreateTableSQLQuery, "content_creator", tableName);
1216            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_creator", "Content author"));
1217            currentCreateTableSQLQuery.append(", ");
1218            
1219            addColumnForDateTimeMetadata(currentCreateTableSQLQuery, "content_creationDate", tableName);
1220            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_creationDate", "Content creation date"));
1221            currentCreateTableSQLQuery.append(", ");
1222            
1223            addColumnForStringMetadata(currentCreateTableSQLQuery, "content_lastContributor", tableName);
1224            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_lastContributor", "Content last contributor"));
1225            currentCreateTableSQLQuery.append(", ");
1226            
1227            addColumnForDateTimeMetadata(currentCreateTableSQLQuery, "content_lastModificationDate", tableName);
1228            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_lastModificationDate", "Content last modification date"));
1229            currentCreateTableSQLQuery.append(", ");
1230            
1231            addColumnForDateTimeMetadata(currentCreateTableSQLQuery, "content_lastValidationDate", tableName);
1232            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_lastValidationDate", "Content last validation date"));
1233            currentCreateTableSQLQuery.append(", ");
1234            
1235            addColumnForDateTimeMetadata(currentCreateTableSQLQuery, "content_lastMajorValidationDate", tableName);
1236            currentCreateTableSQLQuery.append(createCommentQuery(tableName, "content_lastMajorValidationDate", "Content last major validation date"));
1237        }
1238    }
1239    
1240    /**
1241     * Create the query to add comment to a table or a column
1242     * @param table the table name
1243     * @param column the column name
1244     * @param comments the comments
1245     * @return the query to add comment to a table or a column
1246     * @throws ContextException if a context error occurred 
1247     */
1248    protected String createCommentQuery(String table, String column, String comments) throws ContextException
1249    {
1250        String normalizedComment = _normalizeNameComponent.normalizedComment(comments, StringUtils.isEmpty(column) ? _commentTableMaxLength : _commentColumnMaxLength, _connection);
1251        
1252        String commentSql = "";
1253        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
1254        {
1255            commentSql = " COMMENT '" + normalizedComment + "'";
1256        }
1257        else if (_databaseType.equals(ConnectionHelper.DATABASE_ORACLE)) //TODO
1258        {
1259            /*if (StringUtils.isNotEmpty(table)) 
1260            {
1261                if (StringUtils.isEmpty(column)) 
1262                {
1263//                    _tablesInfos.get(table).addCommentQuery("COMMENT ON TABLE " + _normaliseNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, table, _connection) + " IS '" + normalizedComment + "'");
1264                }
1265                else
1266                {
1267//                    _tablesInfos.get(table).addCommentQuery("COMMENT ON COLUMN " + _normaliseNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, table, _connection) + "." + _normaliseNameComponent.normalizedColumnName(_mappingPolicy, column, table, _connection) + " IS '" + normalizedComment + "'");
1268                }
1269            }*/
1270        }
1271        
1272        return commentSql;
1273    }
1274    
1275    /**
1276     * Create the query to add foreign key
1277     * @param fkName the foreign key name
1278     * @param tableName the table name 
1279     * @param columnName the column name 
1280     * @return the query to add comment to a table or a column
1281     * @throws ContextException if a context error occurred 
1282     */
1283    protected String createForeignKeyQuery(String fkName, String tableName, String columnName) throws ContextException
1284    {
1285        StringBuilder foreignKeySQLQuery = new StringBuilder();
1286        foreignKeySQLQuery.append(", CONSTRAINT fk_");
1287        foreignKeySQLQuery.append(_fkIndice);
1288        _fkIndice++;
1289
1290        foreignKeySQLQuery.append(" FOREIGN KEY (");
1291        foreignKeySQLQuery.append(fkName);
1292        foreignKeySQLQuery.append(")");
1293        foreignKeySQLQuery.append(" REFERENCES ");
1294        foreignKeySQLQuery.append(tableName);
1295        foreignKeySQLQuery.append(" (");
1296        foreignKeySQLQuery.append(columnName);
1297        foreignKeySQLQuery.append(")");
1298        
1299        return foreignKeySQLQuery.toString();
1300    }
1301    
1302    /**
1303     * Create the query to add engine
1304     * @return the query to add engine
1305     * @throws ContextException if a context error occurred 
1306     */
1307    protected String createEngineQuery() throws ContextException
1308    {
1309        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
1310        {
1311            return "ENGINE=" + MYSQL_CONTENT_EXPORT_ENGINE + " ROW_FORMAT=COMPRESSED DEFAULT CHARACTER SET " + MYSQL_CONTENT_EXPORT_CHARSET + " ";
1312        }
1313        else if (_databaseType.equals(ConnectionHelper.DATABASE_ORACLE))
1314        {
1315            return "STORAGE (INITIAL 8K NEXT 8K)";
1316        }
1317
1318        return "";
1319    }
1320    
1321    /**
1322     * Create the query to add primary key
1323     * @return the query to add primary key
1324     * @throws ContextException if a context error occurred 
1325     */
1326    protected String createPrimaryKeyQuery() throws ContextException
1327    {
1328        return " PRIMARY KEY NOT NULL";
1329    }
1330
1331    /**
1332     * Return the sql type depends on the metadata and the database type
1333     * @param metadataType the metadata type
1334     * @return the sql type depends on the metadata and the database type
1335     */
1336    protected String convertMetadataTypeToSql(String metadataType)
1337    {
1338        String sqlType = "";
1339        
1340        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
1341        {
1342            sqlType = _mappingSql.get("mysql").get(metadataType);
1343        }
1344        else if (_databaseType.equals(ConnectionHelper.DATABASE_ORACLE))
1345        {
1346            sqlType = _mappingSql.get("oracle").get(metadataType);
1347        }
1348        
1349        return sqlType;
1350    }
1351    
1352    
1353    /**
1354     * Execute SQL queries
1355     * @throws SQLException if a sql error occurred
1356     * @throws IOException if an IO error occurred
1357     */
1358    protected void executeSqlQueries() throws SQLException, IOException
1359    {
1360        int nbTotalTable = _getNbTable();
1361
1362        boolean isInfoEnabled = getLogger().isInfoEnabled();
1363        if (isInfoEnabled)
1364        {
1365            List<String> i18nParams = new ArrayList<>();
1366            i18nParams.add(String.valueOf(nbTotalTable));
1367            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_BEGINNING", i18nParams)));
1368        }
1369
1370        int nbTableCreated = 0;
1371        int limitPourcentReport = 10;
1372        try
1373        {
1374            for (Entry<String, ExportTableInfo> entry : _tablesInfos.entrySet()) 
1375            {
1376                ExportTableInfo tableInfo = entry.getValue();
1377                
1378                List<String> listQuery = tableInfo.getCreateQueries();
1379                _executeQueryFromlistQuery(listQuery);
1380                nbTableCreated += listQuery.size();
1381                
1382                int pourcent = nbTableCreated * 100 / nbTotalTable;
1383                if (pourcent >= limitPourcentReport)
1384                {
1385                    if (isInfoEnabled)
1386                    {
1387                        List<String> i18nParams = new ArrayList<>();
1388                        i18nParams.add(String.valueOf(limitPourcentReport));
1389                        getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_ADVANCE", i18nParams)));
1390                    }
1391                    limitPourcentReport += 10;
1392                }
1393                
1394                _executeQueryFromlistQuery(tableInfo.getCommentQueries());
1395                _executeQueryFromlistQuery(tableInfo.getInsertQueries());
1396            }
1397        }
1398        catch (IOException e)
1399        {
1400            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_GET_FILE_ERROR")), e);
1401        }
1402        finally
1403        {
1404            if (isInfoEnabled)
1405            {
1406                List<String> i18nParams = new ArrayList<>();
1407                i18nParams.add(String.valueOf(nbTableCreated));
1408                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_FINISH", i18nParams)));
1409            }
1410        }
1411    }
1412    
1413    private int _getNbTable()
1414    {
1415        int nbTable = 0;
1416        for (Entry<String, ExportTableInfo> entry : _tablesInfos.entrySet())
1417        {
1418            ExportTableInfo tableInfo = entry.getValue();
1419            
1420            List<String> listQuery = tableInfo.getCreateQueries();
1421            nbTable += listQuery.size();
1422        }
1423        
1424        return nbTable;
1425    }
1426    
1427    /**
1428     * Execute query list
1429     * @param listQuery the list of query to execute
1430     * @throws SQLException if a sql error occurred
1431     * @throws IOException if an IO error occurred
1432     */
1433    protected void _executeQueryFromlistQuery(List<String> listQuery) throws SQLException, IOException
1434    {
1435        for (String query : listQuery)
1436        {
1437            getLogger().debug(query);
1438
1439            PreparedStatement stmt = null;
1440            try
1441            {
1442                stmt = _connection.prepareStatement(query);
1443                stmt.execute();
1444            }
1445            catch (SQLException e)
1446            {
1447                throw new SQLException("The SQL query failed : " + query, e);
1448            }
1449            finally
1450            {
1451                // Close the connection resources
1452                ConnectionHelper.cleanup(stmt);
1453            }
1454        }
1455    }
1456    
1457    /**
1458     * Prepare INSERT statement
1459     * @param tableName the table name
1460     * @return the INSERT preparedStatement
1461     * @throws SQLException if a sql error occurred
1462     * @throws ContextException if a context error occurred 
1463     */
1464    protected PreparedStatement getInsertPreparedStatementFromTableName(String tableName) throws SQLException, ContextException
1465    {
1466        StringBuilder sql = new StringBuilder();
1467        sql.append("INSERT INTO ");
1468        sql.append(_normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection));
1469        sql.append(" VALUES ( ?");
1470        
1471        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1472
1473        for (int i = 1; i < tableInfo.getNbColumns(); i++)
1474        {
1475            sql.append(", ?");
1476        }
1477        sql.append(")");
1478        
1479        PreparedStatement stmt = _connection.prepareStatement(sql.toString());
1480        return stmt;
1481    }
1482}