Source code

001/*
002 *  Copyright 2018 Anyware Services
003 *
004 *  Licensed under the Apache License, Version 2.0 (the "License");
005 *  you may not use this file except in compliance with the License.
006 *  You may obtain a copy of the License at
007 *
008 *      http://www.apache.org/licenses/LICENSE-2.0
009 *
010 *  Unless required by applicable law or agreed to in writing, software
011 *  distributed under the License is distributed on an "AS IS" BASIS,
012 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 *  See the License for the specific language governing permissions and
014 *  limitations under the License.
015 */
016package org.ametys.plugins.contentio.export.sql;
017
018import java.io.IOException;
019import java.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.service.ServiceException;
033import org.apache.avalon.framework.service.ServiceManager;
034import org.apache.avalon.framework.service.Serviceable;
035import org.apache.commons.lang.StringUtils;
036
037import org.ametys.cms.contenttype.ContentType;
038import org.ametys.cms.contenttype.ContentTypeExtensionPoint;
039import org.ametys.cms.data.type.ModelItemTypeConstants;
040import org.ametys.cms.languages.LanguagesManager;
041import org.ametys.core.datasource.ConnectionHelper;
042import org.ametys.core.util.I18nUtils;
043import org.ametys.plugins.repository.model.CompositeDefinition;
044import org.ametys.plugins.repository.model.RepeaterDefinition;
045import org.ametys.runtime.config.Config;
046import org.ametys.runtime.i18n.I18nizableText;
047import org.ametys.runtime.model.ElementDefinition;
048import org.ametys.runtime.model.ModelItem;
049import org.ametys.runtime.model.ModelItemContainer;
050import org.ametys.runtime.plugin.component.AbstractLogEnabled;
051
052/**
053 *  Create SQl Table Component
054 */
055public class CreateSqlTableComponent extends AbstractLogEnabled implements Component, Serviceable
056{
057    /** The component role */
058    public static final String ROLE = CreateSqlTableComponent.class.getName();
059    
060    /** Code of default language for comments */
061    public static final String DEFAULT_LANGUAGE_CODE_FOR_COMMENTS = "en";
062    
063    /** The engine */
064    public static final String MYSQL_CONTENT_EXPORT_ENGINE = "MyISAM";
065    
066    /** The encoding */
067    public static final String MYSQL_CONTENT_EXPORT_CHARSET = "UTF8MB4";
068    
069    /** Prefix for parent table column */
070    public static final String COLUMN_PARENT_TABLE_PREFIX = "PID_";
071    
072    private static final Pattern _MYSQL_VERSION_NUMBER_EXTRACT = Pattern.compile("^([0-9]+).*$");
073    
074    /** Content type extension point. */
075    protected ContentTypeExtensionPoint _contentTypeExtensionPoint;
076    
077    /** The i18n translator. */
078    protected I18nUtils _i18nTranslator;
079    
080    /** The normalise name component. */
081    protected NormalizeNameComponent _normalizeNameComponent;
082    
083    /** The language manager */
084    protected LanguagesManager _languageManager;
085    
086    private Connection _connection;
087    private String _databaseType;
088    
089    private LinkedHashMap<String, ExportTableInfo> _tablesInfos;
090    private int _commentTableMaxLength;
091    private int _commentColumnMaxLength;
092    private String _sqlTablePrefix;
093    private String _sqlPrefixConf;
094    private Map<String, Map<String, String>> _mapping;
095    private Map<String, Map<String, String>> _reservedWords;
096    private String _mappingPolicy;
097    private ArrayList<String> _mappingTablesQueries;
098    private boolean _exportNoMultiValuedTable;
099    
100    private int _fkIndice;
101    private int _pkIndice;
102    
103    @Override
104    public void service(ServiceManager manager) throws ServiceException
105    {
106        _i18nTranslator = (I18nUtils) manager.lookup(I18nUtils.ROLE);
107        _contentTypeExtensionPoint = (ContentTypeExtensionPoint) manager.lookup(ContentTypeExtensionPoint.ROLE);
108        _normalizeNameComponent = (NormalizeNameComponent) manager.lookup(NormalizeNameComponent.ROLE);
109        _languageManager = (LanguagesManager) manager.lookup(LanguagesManager.ROLE);
110    } 
111    
112    /**
113     * Create sql tables for contents
114     * @param exportConfiguration the content export configuration
115     * @return tablesInfos
116     * @throws SQLException if a sql error occurred
117     * @throws IOException if an IO error occurred
118     */
119    public synchronized Map<String, ExportTableInfo> createTables(ExportConfiguration exportConfiguration) throws SQLException, IOException
120    {
121        // Get from configuration
122        _sqlPrefixConf = exportConfiguration.getTablePrefix();
123        _sqlTablePrefix = exportConfiguration.getTablePrefix();
124        _mapping = exportConfiguration.getMappingSql();
125        _mappingPolicy = exportConfiguration.getMappingPolicy();
126        _reservedWords = exportConfiguration.getReservedWords();
127        _exportNoMultiValuedTable = exportConfiguration.exportNoMultiValuedTable();
128        
129        // Initialization
130        _fkIndice = 1;
131        _pkIndice = 1;
132        _tablesInfos = new LinkedHashMap<>();
133        _mappingTablesQueries = new ArrayList<>();
134        
135        try
136        {
137            String datasourceId = Config.getInstance().getValue("org.ametys.plugins.contentio.content.export.datasource");
138            _connection = ConnectionHelper.getConnection(datasourceId); 
139            _databaseType = ConnectionHelper.getDatabaseType(_connection);
140            
141            String productVersion = _connection.getMetaData().getDatabaseProductVersion();
142            initialize(productVersion);
143            
144            boolean isInfoEnabled = getLogger().isInfoEnabled();
145            if (isInfoEnabled)
146            {
147                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_BEGIN")));
148            }
149            
150            createTablesInfos(exportConfiguration.getContentTypesToExport());
151            
152            if (isInfoEnabled)
153            {
154                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_END")));
155                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_BEGIN")));
156            }
157            
158            createMappingTables();
159            
160            if (isInfoEnabled)
161            {
162                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_END")));
163            }
164            
165            createRichTextDataTable();
166            createContentTable();
167            
168            executeQueries();
169        }
170        finally
171        {
172            ConnectionHelper.cleanup(_connection);
173        }
174        
175        return _tablesInfos;
176    }
177
178    /**
179     * Initialization
180     * @param productVersion The database product version
181     */
182    protected void initialize(String productVersion)
183    {
184        // Get the maximun number of authorized characters for table and column comments
185        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
186        {
187            String[] parts = productVersion.split("\\.");
188            
189            int majorVersion = Integer.parseInt(parts[0]);
190            int minorVersion = 0;
191            int patchVersion = 0;
192            if (parts.length > 1)
193            {
194                Matcher matcher = _MYSQL_VERSION_NUMBER_EXTRACT.matcher(parts[1]);
195                if (matcher.matches())
196                {
197                    minorVersion = Integer.parseInt(matcher.group(1));
198                }
199            }
200            
201            if (parts.length > 2)
202            {
203                Matcher matcher = _MYSQL_VERSION_NUMBER_EXTRACT.matcher(parts[2]);
204                if (matcher.matches())
205                {
206                    patchVersion = Integer.parseInt(matcher.group(1));
207                }
208            }
209            
210            if (majorVersion > 5 || majorVersion >= 5 && minorVersion > 5 || majorVersion >= 5 && minorVersion >= 5 && patchVersion >= 3)
211            {
212                // Version 5.5.3 or later
213                _commentTableMaxLength = 2048;
214                _commentColumnMaxLength = 1024;
215            }
216            else
217            {
218                // Version before 5.5.3
219                _commentTableMaxLength = 60;
220                _commentColumnMaxLength = 255;
221            }
222        }
223        else
224        {
225            // No max
226            _commentTableMaxLength = 2048;
227            _commentColumnMaxLength = 1024;
228        }
229    }
230    
231    /**
232     * Created all tables informations
233     * @param contents to export
234     * @throws SQLException if a sql error occurred
235     */
236    protected void createTablesInfos(Map<String, String> contents) throws SQLException
237    {
238        for (Entry<String, String> entry : contents.entrySet()) 
239        {
240            String contentTypeId = entry.getKey();
241
242            ContentType contentType = _contentTypeExtensionPoint.getExtension(contentTypeId);
243            if (!contentType.isAbstract())
244            {
245                String tableName = entry.getValue();
246                String comment = _i18nTranslator.translate(contentType.getLabel(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS) + ": " + _i18nTranslator.translate(contentType.getDescription(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
247                createQueriesForTableCreation(contentType, tableName, null, comment, false);
248            }
249            
250            _sqlTablePrefix = _sqlPrefixConf;
251        }
252    }
253    
254    /**
255     * Create SQL queries to create necessary SQL tables for content type export
256     * @param modelItemContainer the current {@link ModelItemContainer}
257     * @param tableName the table name The SQL table name
258     * @param tableParentName  The SQL parent table name
259     * @param comment the comment
260     * @param isSortTable true if table's rows have to be ordered
261     */
262    protected void createQueriesForTableCreation(ModelItemContainer modelItemContainer, String tableName, String tableParentName, String comment, boolean isSortTable)
263    {
264        ExportTableInfo tableInfo = new ExportTableInfo(tableName);
265        tableInfo.incrementNbColumns();
266        
267        String tableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
268        _tablesInfos.put(tableName, tableInfo);
269        
270        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
271        
272        currentCreateTableSQLQuery.append("CREATE TABLE ");
273        currentCreateTableSQLQuery.append(tableNameNormalized);
274        currentCreateTableSQLQuery.append(" (");
275        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, "id_" + tableName, tableName, _reservedWords, _connection));
276        currentCreateTableSQLQuery.append(" VARCHAR(250)");
277        currentCreateTableSQLQuery.append(createPrimaryKeyQuery());
278        currentCreateTableSQLQuery.append(createCommentQuery(tableName, "id_" + tableName, "Ametys ID"));
279        
280        if (StringUtils.isNotEmpty(tableParentName))
281        {
282            currentCreateTableSQLQuery.append(", ");
283            addColumnParentId(currentCreateTableSQLQuery, tableParentName, tableName);
284        }
285        
286        if (isSortTable)
287        {
288            currentCreateTableSQLQuery.append(", ");
289            addSortColumn(currentCreateTableSQLQuery, tableName);
290        }
291        
292        addColumnForContainer(modelItemContainer, currentCreateTableSQLQuery, tableName, "");
293        
294        if (modelItemContainer instanceof ContentType)
295        {
296            addAdditionalData((ContentType) modelItemContainer, currentCreateTableSQLQuery, tableName);
297        }
298        
299        currentCreateTableSQLQuery.append(") ");
300        currentCreateTableSQLQuery.append(createEngineQuery());
301        currentCreateTableSQLQuery.append(createCommentQuery(tableName, null, comment));
302        
303        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
304    }  
305    
306    /**
307     * Create table for images in rich text
308     */
309    protected void createRichTextDataTable()
310    {
311        String dataTableName = _sqlTablePrefix + ExportManager.RICH_TEXT_DATA_TABLE_NAME;
312        ExportTableInfo tableInfo = new ExportTableInfo(dataTableName);
313        tableInfo.incrementNbColumns(8);
314
315        String dateTableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", dataTableName, _connection);
316        _tablesInfos.put(dataTableName, tableInfo);
317        
318        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
319
320        currentCreateTableSQLQuery.append("CREATE TABLE ");
321        currentCreateTableSQLQuery.append(dateTableNameNormalized);
322        currentCreateTableSQLQuery.append(" (id_data VARCHAR(250)");
323        currentCreateTableSQLQuery.append(createPrimaryKeyQuery());
324        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "id_data", "Data ID"));
325        currentCreateTableSQLQuery.append(", id_content VARCHAR(255)");
326        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "id_content", "Parent ID"));
327        
328        currentCreateTableSQLQuery.append(", attribute_name ");
329        currentCreateTableSQLQuery.append(convertTypeToSql("string"));
330        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "attribute_name", "Richtext attribute name"));
331        currentCreateTableSQLQuery.append(", data_name ");
332        currentCreateTableSQLQuery.append(convertTypeToSql("string"));
333        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_name", "Name"));
334        
335        currentCreateTableSQLQuery.append(", data ");
336        currentCreateTableSQLQuery.append(convertTypeToSql("file"));
337        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data", "Data"));
338        currentCreateTableSQLQuery.append(", data_mimetype VARCHAR(255)");
339        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_mimetype", "Mime type"));
340        currentCreateTableSQLQuery.append(", data_size INT");
341        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_size", "Size"));
342        currentCreateTableSQLQuery.append(", data_lastmodified ");
343        currentCreateTableSQLQuery.append(convertTypeToSql("datetime"));
344        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "data_lastmodified", "Last modification date"));
345        currentCreateTableSQLQuery.append(", ");
346        
347        addSortColumn(currentCreateTableSQLQuery, dataTableName);
348        
349        currentCreateTableSQLQuery.append(") ");
350        currentCreateTableSQLQuery.append(createEngineQuery());
351
352        String comment = "Data table of all rich text";
353        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, null, comment));
354        
355        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
356    }
357    
358    /**
359     * Create table for content
360     */
361    protected void createContentTable()
362    {
363        String dataTableName = _sqlPrefixConf + ExportManager.CONTENT_TABLE_NAME;
364        ExportTableInfo tableInfo = new ExportTableInfo(dataTableName);
365        tableInfo.incrementNbColumns(2);
366
367        String dataTableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", dataTableName, _connection);
368        _tablesInfos.put(dataTableName, tableInfo);
369        
370        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
371
372        currentCreateTableSQLQuery.append("CREATE TABLE ");
373        currentCreateTableSQLQuery.append(dataTableNameNormalized);
374        currentCreateTableSQLQuery.append(" (id_content VARCHAR(80)");
375        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "id_content", "Content ID"));
376        currentCreateTableSQLQuery.append(", table_name VARCHAR(170)");
377        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, "table_name", "Table name"));
378        currentCreateTableSQLQuery.append(", CONSTRAINT pk_content PRIMARY KEY (id_content, table_name) ");
379        currentCreateTableSQLQuery.append(") ");
380        currentCreateTableSQLQuery.append(createEngineQuery());
381
382        String comment = "Link table of content with it's own table";
383        currentCreateTableSQLQuery.append(createCommentQuery(dataTableName, null, comment));
384        
385        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
386    }
387    
388    /**
389     * Create the table for the enumerator (key, value)
390     * @param definition the attribute definition
391     * @param tableParentName the table parent name
392     * @param tableName the table name
393     * @param columnName the column name
394     * @param currentCreateTableSQLQuery the current SQL create table query 
395     */
396    protected void createTableForEnumerator(ElementDefinition definition, String tableParentName, String tableName, String columnName, StringBuilder currentCreateTableSQLQuery)
397    {
398        ExportTableInfo tableInfo = new ExportTableInfo(tableName);
399        tableInfo.incrementNbColumns();
400        
401        String tableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
402        _tablesInfos.put(tableName, tableInfo);
403        
404        StringBuilder sql = new StringBuilder();
405        
406        sql.append("CREATE TABLE ");
407        sql.append(tableNameNormalized);
408        sql.append(" (key_enum ");
409        sql.append(definition.getType().getId().equals(org.ametys.runtime.model.type.ModelItemTypeConstants.LONG_TYPE_ID) ? "INT" : "VARCHAR(250)");
410        sql.append(createPrimaryKeyQuery());
411        sql.append(createCommentQuery(tableName, "key_enum_" + definition.getName(), "Enumerator key"));
412        
413        addColumn(sql, "value_enum_" + definition.getName(), tableName, null, "string");
414        
415        sql.append(") ");
416        sql.append(createEngineQuery());
417        sql.append(createCommentQuery(tableName, null, "Enumerator table " + definition.getName() + " linked to the table " + tableParentName));
418        
419        tableInfo.addCreateQuery(sql.toString());
420        
421//        String fkName = _normaliseNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableParentName, _connection);
422//        String fkTableName = _normaliseNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
423//        String fkColumnName = "key_enum";
424//        currentCreateTableSQLQuery.append(createForeignKeyQuery(fkName, fkTableName, fkColumnName));
425        //TODO foreign key ??
426        
427        fillTableForEnumerator(definition, tableName);
428    }
429    
430    /**
431     * Fill values for the enumerator table
432     * @param definition the attribute definition
433     * @param tableName the table name
434     */
435    protected void fillTableForEnumerator(ElementDefinition definition, String tableName)
436    {   
437        try
438        {
439            for (Entry<Object, I18nizableText> entry : ((Map<Object, I18nizableText>) definition.getEnumerator().getTypedEntries()).entrySet()) 
440            {
441                String enumValue = _i18nTranslator.translate(entry.getValue(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
442                String enumKey = entry.getKey().toString();
443
444                StringBuilder sql = new StringBuilder();
445                sql.append("INSERT INTO ");
446                sql.append(_normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection));
447                sql.append(" VALUES ('");
448                sql.append(_normalizeNameComponent.escapeValue(enumKey, _connection));
449                sql.append("', '");
450                sql.append(_normalizeNameComponent.escapeValue(enumValue, _connection));
451                sql.append("')");
452
453                _tablesInfos.get(tableName).addInsertQuery(sql.toString());
454            }
455        }
456        catch (Exception e)
457        {
458            getLogger().warn(e.getMessage(), e);
459        }
460    }
461    
462    /**
463     * Create the two table mapping (for table name and column name)
464     */
465    protected void createMappingTables()
466    {
467        PreparedStatement stmt = null;
468        String mappingTableName = _sqlTablePrefix + ExportManager.MAPPING_TABLE_NAME;
469        String mappingColumnName = _sqlTablePrefix + ExportManager.MAPPING_COLUMN_NAME;
470        
471        ExportTableInfo mappingTableInfo = new ExportTableInfo(mappingTableName);
472        mappingTableInfo.incrementNbColumns(3);
473
474        ExportTableInfo mappingColumnInfo = new ExportTableInfo(mappingColumnName);
475        mappingColumnInfo.incrementNbColumns(3);
476
477        String mappingTableNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", mappingTableName, _connection);
478        String mappingColumnNameNormalized = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, "FULL", mappingColumnName, _connection);
479        
480        _tablesInfos.put(mappingTableName, mappingTableInfo);
481        _tablesInfos.put(mappingColumnName, mappingColumnInfo);
482        
483        StringBuilder createMappingTableSQLQuery = new StringBuilder();
484
485        createMappingTableSQLQuery.append("CREATE TABLE ");
486        createMappingTableSQLQuery.append(mappingTableNameNormalized);
487        createMappingTableSQLQuery.append(" (id_table INT");
488        createMappingTableSQLQuery.append(createPrimaryKeyQuery());
489        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, "id_table", "Table name ID"));
490        createMappingTableSQLQuery.append(", real_name VARCHAR(512)");
491        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, "real_name", "Real name"));
492        createMappingTableSQLQuery.append(", modified_name VARCHAR(512)");
493        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, "modified_name", "Normalized name"));
494        
495        createMappingTableSQLQuery.append(") ");
496        createMappingTableSQLQuery.append(createEngineQuery());
497
498        String comment = "Mapping table between real name and normalized name.";
499        createMappingTableSQLQuery.append(createCommentQuery(mappingTableName, null, comment));
500        
501        try
502        {
503            _mappingTablesQueries.add(createMappingTableSQLQuery.toString());
504            stmt = _connection.prepareStatement(createMappingTableSQLQuery.toString());
505            stmt.execute();
506        }
507        catch (SQLException e)
508        {
509            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_TABLE_ERROR")), e);
510        }
511        finally
512        {
513            ConnectionHelper.cleanup(stmt);
514        }
515        
516        StringBuilder createMappingColumnSQLQuery = new StringBuilder();
517
518        createMappingColumnSQLQuery.append("CREATE TABLE ");
519        createMappingColumnSQLQuery.append(mappingColumnNameNormalized);
520        createMappingColumnSQLQuery.append(" (id_table INT");
521        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, "id_table", "Table name id"));
522        createMappingColumnSQLQuery.append(createForeignKeyQuery("id_table", _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, mappingTableName, _connection), "id_table"));
523        createMappingColumnSQLQuery.append(", real_name VARCHAR(512)");
524        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, "real_name", "Real column name"));
525        createMappingColumnSQLQuery.append(", modified_name VARCHAR(512)");
526        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, "modified_name", "Normalized column name"));
527        
528        createMappingColumnSQLQuery.append(") ");
529        createMappingColumnSQLQuery.append(createEngineQuery());
530
531        String commentColum = "Mapping table between real column name and normalized column name.";
532        createMappingColumnSQLQuery.append(createCommentQuery(mappingColumnName, null, commentColum));
533        
534        try
535        {
536            _mappingTablesQueries.add(createMappingColumnSQLQuery.toString());
537            stmt = _connection.prepareStatement(createMappingColumnSQLQuery.toString());
538            stmt.execute();
539        }
540        catch (SQLException e)
541        {
542            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_MAPPING_COLUMN_ERROR")), e);
543        }
544        finally
545        {
546            ConnectionHelper.cleanup(stmt);
547        }
548        
549        fillMappingTables(mappingTableName, mappingColumnName);
550    }
551    
552    /**
553     * Fill the two mapping table (for table name and column name)
554     * @param mappingTableName the table mapping table name
555     * @param mappingColumnName the column mapping table name
556     */
557    protected void fillMappingTables(String mappingTableName, String mappingColumnName)
558    {
559        HashMap<String, String> mappingTable = (HashMap<String, String>) _normalizeNameComponent.getMappingTableNameFromCache();
560        HashMap<String, HashMap<String, String>> mappingColumn = (HashMap<String, HashMap<String, String>>) _normalizeNameComponent.getMappingTableColumnNameFromCache();
561        
562        PreparedStatement stmtTableName = null;
563        PreparedStatement stmtColumnName = null;
564        try
565        {
566            stmtTableName = getInsertPreparedStatementFromTableName(mappingTableName);
567            stmtColumnName = getInsertPreparedStatementFromTableName(mappingColumnName);
568            int i = 0;
569            for (Entry<String, String> entry : mappingTable.entrySet()) 
570            {
571                String realName = entry.getKey();
572                String modifiedName = entry.getValue();
573                
574                if (getLogger().isDebugEnabled())
575                {
576                    String tableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, mappingTableName, _connection);
577                    getLogger().debug("INSERT INTO {} VALUES ('{}', '{}', '{}')", tableName, i, realName, modifiedName);
578                }
579                
580                stmtTableName.setInt(1, i);
581                stmtTableName.setString(2, realName);
582                stmtTableName.setString(3, modifiedName);
583                
584                stmtTableName.addBatch();
585                
586                if (mappingColumn.containsKey(realName))
587                {
588                    for (Entry<String, String> entryCol : mappingColumn.get(realName).entrySet())
589                    {
590                        String realNameCol = entryCol.getKey();
591                        String modifiedNameCol = entryCol.getValue();
592                        
593                        if (getLogger().isDebugEnabled())
594                        {
595                            String tableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, mappingTableName, _connection);
596                            getLogger().debug("INSERT INTO {} VALUES ('{}', '{}', '{}')", tableName, i, realNameCol, modifiedNameCol);
597                        }
598
599                        stmtColumnName.setInt(1, i);
600                        stmtColumnName.setString(2, realNameCol);
601                        stmtColumnName.setString(3, modifiedNameCol);
602                        
603                        stmtColumnName.addBatch();
604                        
605                    }
606                }
607                i++;
608            }
609            
610            stmtTableName.executeBatch();
611            stmtColumnName.executeBatch();
612        }
613        catch (SQLException e)
614        {
615            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_FILL_MAPPING_ERROR")), e);
616        }
617        finally
618        {
619            ConnectionHelper.cleanup(stmtTableName);
620            ConnectionHelper.cleanup(stmtColumnName);
621        }
622    }
623    
624    /**
625     * Add all the column for each attribute in the {@link ModelItemContainer}.
626     * @param modelItemContainer the attribute definitions
627     * @param currentCreateTableSQLQuery the current SQL create table query
628     * @param tableName the table name
629     * @param columnNamePrefix the column namePrefix
630     */
631    protected void addColumnForContainer(ModelItemContainer modelItemContainer, StringBuilder currentCreateTableSQLQuery, String tableName, String columnNamePrefix)
632    {
633        for (ModelItem modelItem : modelItemContainer.getModelItems())
634        {
635            String name = modelItem.getName();
636            String columnName = columnNamePrefix + name;
637            if (modelItem instanceof ElementDefinition definition)
638            {
639                // simple element
640                if (definition.isMultiple())
641                {
642                    if (_exportNoMultiValuedTable)
643                    {
644                        String comment = _i18nTranslator.translate(definition.getLabel(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS) + ": " + _i18nTranslator.translate(definition.getDescription(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
645                        addColumn(currentCreateTableSQLQuery, columnName, tableName, comment, "string");
646                    }
647                    else
648                    {
649                        createQueryForMultipleAttribute(definition, tableName, tableName + "_" + columnName); 
650                    }
651                }
652                else
653                {
654                    addColumnForSingleAttribute(definition, currentCreateTableSQLQuery, tableName, columnName);
655                    
656                    if (definition.getEnumerator() != null)
657                    {        
658                        createTableForEnumerator(definition, tableName, tableName + "_" + name, columnName, currentCreateTableSQLQuery);
659                    }
660                }
661            }
662            else if (modelItem instanceof CompositeDefinition)
663            {
664                // composite
665                addColumnForContainer((CompositeDefinition) modelItem, currentCreateTableSQLQuery, tableName, columnName + "_");
666            }
667            else if (modelItem instanceof RepeaterDefinition)
668            {
669                // repeater
670                String commentTable = "Repeater " + _normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection) + " linked to the table " + _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
671                createQueriesForTableCreation((RepeaterDefinition) modelItem, tableName + "_" + columnName, tableName, commentTable, true);
672            }
673        }
674    }
675    
676    /**
677     * Add column for single attribute
678     * @param definition the attribute definition
679     * @param currentCreateTableSQLQuery the current SQL create table query
680     * @param tableName the table name
681     * @param columnName the column name
682     */
683    protected void addColumnForSingleAttribute(ElementDefinition definition, StringBuilder currentCreateTableSQLQuery, String tableName, String columnName)
684    {
685        String comment = _i18nTranslator.translate(definition.getLabel(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS) + ": " + _i18nTranslator.translate(definition.getDescription(), DEFAULT_LANGUAGE_CODE_FOR_COMMENTS);
686
687        String type = definition.getType().getId();
688        if (type.equals(ModelItemTypeConstants.USER_ELEMENT_TYPE_ID))
689        {
690            addColumn(currentCreateTableSQLQuery, columnName + "_login", tableName, "User login", "string");
691            addColumn(currentCreateTableSQLQuery, columnName + "_population", tableName, "User population", "string");
692        }
693        else if (type.equals(ModelItemTypeConstants.GEOCODE_ELEMENT_TYPE_ID))
694        {
695            addColumn(currentCreateTableSQLQuery, columnName + "_longitude", tableName, "Longitude of " + columnName, "double");
696            addColumn(currentCreateTableSQLQuery, columnName + "_latitude", tableName, "Latitude type of " + columnName, "double");
697        }
698        else if (type.equals(ModelItemTypeConstants.BINARY_ELEMENT_TYPE_ID) || type.equals(ModelItemTypeConstants.FILE_ELEMENT_TYPE_ID))
699        {
700            String actualColumnName = addColumn(currentCreateTableSQLQuery, columnName, tableName, comment, "string");
701            addColumn(currentCreateTableSQLQuery, actualColumnName + "_data", tableName, "Data of " + columnName, "binary");
702            addColumn(currentCreateTableSQLQuery, actualColumnName + "_mimetype", tableName, "Mime type of " + columnName, "short-string");
703            addColumn(currentCreateTableSQLQuery, actualColumnName + "_size", tableName, "Size of " + columnName, "long");
704            addColumn(currentCreateTableSQLQuery, actualColumnName + "_lastmodified", tableName, "Last modification date of " + columnName, "datetime");
705        }
706        else if (type.equals(ModelItemTypeConstants.MULTILINGUAL_STRING_ELEMENT_TYPE_ID))
707        {
708            for (String lang : _languageManager.getAvailableLanguages().keySet())
709            {
710                addColumn(currentCreateTableSQLQuery, columnName + "_" + lang, tableName, "Value of " + columnName + " for lang " + lang, "string");
711            }
712        }
713        else
714        {
715            addColumn(currentCreateTableSQLQuery, columnName, tableName, comment, type);
716        }
717    }
718    
719    /**
720     * Add a column
721     * @param currentCreateTableSQLQuery the current SQL create table query
722     * @param tableName the table name
723     * @param columnName the desired column name
724     * @param comment the column comment
725     * @param type the column type
726     * @return the actual column name, after normalization of the desired one
727     */
728    protected String addColumn(StringBuilder currentCreateTableSQLQuery, String columnName, String tableName, String comment, String type)
729    {
730        String actualColumnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, columnName, tableName, _reservedWords, _connection);
731        
732        currentCreateTableSQLQuery.append(", ").append(actualColumnName);
733        currentCreateTableSQLQuery.append(" ").append(convertTypeToSql(type));
734        
735        if (comment != null)
736        {
737            currentCreateTableSQLQuery.append(createCommentQuery(tableName, columnName, comment));
738        }
739        
740        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
741        tableInfo.incrementNbColumns();
742        
743        return actualColumnName;
744    }
745    
746    /**
747     * Create table for multiple attribute
748     * @param definition the attribute definition
749     * @param tableParentName the name of the table for the attribute's container
750     * @param tableName the table name
751     */
752    protected void createQueryForMultipleAttribute(ElementDefinition definition, String tableParentName, String tableName)
753    {
754        ExportTableInfo tableInfo = new ExportTableInfo(tableName);
755        tableInfo.incrementNbColumns();
756        
757        String normalizeTableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection);
758        _tablesInfos.put(tableName, tableInfo);
759        
760        String normalizedColumnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, COLUMN_PARENT_TABLE_PREFIX + tableParentName, tableName, _reservedWords, _connection);
761        
762        StringBuilder currentCreateTableSQLQuery = new StringBuilder();
763        currentCreateTableSQLQuery.append("CREATE TABLE ");
764        currentCreateTableSQLQuery.append(normalizeTableName);
765        currentCreateTableSQLQuery.append(" (");
766        currentCreateTableSQLQuery.append(normalizedColumnName);
767        currentCreateTableSQLQuery.append(" VARCHAR(245)");
768        currentCreateTableSQLQuery.append(createCommentQuery(tableName, COLUMN_PARENT_TABLE_PREFIX + tableParentName, "Parent ID of the multiple attribute"));
769        
770        String fkName = normalizedColumnName;
771        String fkTableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableParentName, _connection);
772        String fkColumnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, "id_" + tableParentName, tableParentName, _reservedWords, _connection);
773        currentCreateTableSQLQuery.append(createForeignKeyQuery(fkName, fkTableName, fkColumnName));
774        
775        addColumnForSingleAttribute(definition, currentCreateTableSQLQuery, tableName, definition.getName());
776        
777        currentCreateTableSQLQuery.append(", ");
778        addSortColumn(currentCreateTableSQLQuery, tableName);
779        
780        String primaryKey = "pk_" + _pkIndice;
781        _pkIndice++;
782        
783        currentCreateTableSQLQuery.append(", CONSTRAINT ");
784        currentCreateTableSQLQuery.append(primaryKey);
785        currentCreateTableSQLQuery.append(" PRIMARY KEY (");
786        currentCreateTableSQLQuery.append(normalizedColumnName);
787        currentCreateTableSQLQuery.append(", position)");
788        currentCreateTableSQLQuery.append(") ");
789        currentCreateTableSQLQuery.append(createEngineQuery());
790        
791        String comment = "Multiple attribute " + definition.getName() + " linked to the table" + _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableParentName, _connection);
792        currentCreateTableSQLQuery.append(createCommentQuery(tableName, null, comment));
793        
794        tableInfo.addCreateQuery(currentCreateTableSQLQuery.toString());
795    }
796    
797    /**
798     * Add additionnal data for content (title, type, language, creator, creationDate, ....)
799     * @param contentType the model
800     * @param currentCreateTableSQLQuery the current SQL create table query
801     * @param tableName the table name
802     */
803    protected void addAdditionalData(ContentType contentType, StringBuilder currentCreateTableSQLQuery, String tableName)
804    {
805        addColumn(currentCreateTableSQLQuery, "content_title", tableName, "Content title", "string");
806        addColumn(currentCreateTableSQLQuery, "content_type", tableName, "Content type", "string");
807        addColumn(currentCreateTableSQLQuery, "content_language", tableName, "Content lang", "string");
808        addColumn(currentCreateTableSQLQuery, "content_creator", tableName, "Content author", "string");
809        addColumn(currentCreateTableSQLQuery, "content_creationDate", tableName, "Content creation date", "datetime");
810        addColumn(currentCreateTableSQLQuery, "content_lastContributor", tableName, "Content last contributor", "string");
811        addColumn(currentCreateTableSQLQuery, "content_lastModificationDate", tableName, "Content last modification date", "datetime");
812        addColumn(currentCreateTableSQLQuery, "content_lastValidationDate", tableName, "Content last validation date", "datetime");
813        addColumn(currentCreateTableSQLQuery, "content_lastMajorValidationDate", tableName, "Content last major validation date", "datetime");
814    }
815    
816    /**
817     * Add a column to sort
818     * @param currentCreateTableSQLQuery the current SQL create table query
819     * @param tableName the table name
820     */
821    protected void addSortColumn(StringBuilder currentCreateTableSQLQuery, String tableName)
822    {
823        currentCreateTableSQLQuery.append("position INT");
824        currentCreateTableSQLQuery.append(createCommentQuery(tableName, "position", "Order of the row"));
825        
826        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
827        tableInfo.incrementNbColumns();
828    }
829    
830    /**
831     * Add an id column which refer to another table
832     * @param currentCreateTableSQLQuery the current SQL create table query
833     * @param tableParentName the table parent name
834     * @param tableName the table name
835     */
836    protected void addColumnParentId(StringBuilder currentCreateTableSQLQuery, String tableParentName, String tableName)
837    {
838        currentCreateTableSQLQuery.append(_normalizeNameComponent.normalizedColumnName(_mappingPolicy, COLUMN_PARENT_TABLE_PREFIX + tableParentName, tableName, _reservedWords, _connection));
839        currentCreateTableSQLQuery.append(" VARCHAR(250)"); //TODO NOT NULL
840        currentCreateTableSQLQuery.append(createCommentQuery(tableName, COLUMN_PARENT_TABLE_PREFIX + tableParentName, "Parent table ID " + tableParentName));
841
842        String fkName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, COLUMN_PARENT_TABLE_PREFIX + tableParentName, tableName, _reservedWords, _connection);
843        String fkTableName = _normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableParentName, _connection);
844        String fkColumnName = _normalizeNameComponent.normalizedColumnName(_mappingPolicy, "id_" + tableParentName, tableParentName, _reservedWords, _connection);
845        currentCreateTableSQLQuery.append(createForeignKeyQuery(fkName, fkTableName, fkColumnName));
846        
847        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
848        tableInfo.incrementNbColumns();
849    }
850    
851    /**
852     * Create the query to add comment to a table or a column
853     * @param table the table name
854     * @param column the column name
855     * @param comments the comments
856     * @return the query to add comment to a table or a column
857     */
858    protected String createCommentQuery(String table, String column, String comments)
859    {
860        String normalizedComment = _normalizeNameComponent.normalizedComment(comments, StringUtils.isEmpty(column) ? _commentTableMaxLength : _commentColumnMaxLength, _connection);
861        
862        String commentSql = "";
863        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
864        {
865            commentSql = " COMMENT '" + normalizedComment + "'";
866        }
867        else if (_databaseType.equals(ConnectionHelper.DATABASE_ORACLE)) //TODO
868        {
869            /*if (StringUtils.isNotEmpty(table)) 
870            {
871                if (StringUtils.isEmpty(column)) 
872                {
873//                    _tablesInfos.get(table).addCommentQuery("COMMENT ON TABLE " + _normaliseNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, table, _connection) + " IS '" + normalizedComment + "'");
874                }
875                else
876                {
877//                    _tablesInfos.get(table).addCommentQuery("COMMENT ON COLUMN " + _normaliseNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, table, _connection) + "." + _normaliseNameComponent.normalizedColumnName(_mappingPolicy, column, table, _connection) + " IS '" + normalizedComment + "'");
878                }
879            }*/
880        }
881        
882        return commentSql;
883    }
884    
885    /**
886     * Create the query to add foreign key
887     * @param fkName the foreign key name
888     * @param tableName the table name 
889     * @param columnName the column name 
890     * @return the query to add comment to a table or a column
891     */
892    protected String createForeignKeyQuery(String fkName, String tableName, String columnName)
893    {
894        StringBuilder foreignKeySQLQuery = new StringBuilder();
895        foreignKeySQLQuery.append(", CONSTRAINT fk_");
896        foreignKeySQLQuery.append(_fkIndice);
897        _fkIndice++;
898
899        foreignKeySQLQuery.append(" FOREIGN KEY (");
900        foreignKeySQLQuery.append(fkName);
901        foreignKeySQLQuery.append(")");
902        foreignKeySQLQuery.append(" REFERENCES ");
903        foreignKeySQLQuery.append(tableName);
904        foreignKeySQLQuery.append(" (");
905        foreignKeySQLQuery.append(columnName);
906        foreignKeySQLQuery.append(")");
907        
908        return foreignKeySQLQuery.toString();
909    }
910    
911    /**
912     * Create the query to add engine
913     * @return the query to add engine
914     */
915    protected String createEngineQuery()
916    {
917        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
918        {
919            return "ENGINE=" + MYSQL_CONTENT_EXPORT_ENGINE + " ROW_FORMAT=COMPRESSED DEFAULT CHARACTER SET " + MYSQL_CONTENT_EXPORT_CHARSET + " ";
920        }
921        else if (_databaseType.equals(ConnectionHelper.DATABASE_ORACLE))
922        {
923            return "STORAGE (INITIAL 8K NEXT 8K)";
924        }
925
926        return "";
927    }
928    
929    /**
930     * Create the query to add primary key
931     * @return the query to add primary key
932     */
933    protected String createPrimaryKeyQuery()
934    {
935        return " PRIMARY KEY NOT NULL";
936    }
937
938    /**
939     * Return the sql type corresponding to the attribute and the database type.<br>
940     * Default to the sql type corresponding to "string" in case of unknown type.
941     * @param type the attribute's type
942     * @return the sql type
943     */
944    protected String convertTypeToSql(String type)
945    {
946        Map<String, String> mapping = null;
947        if (_databaseType.equals(ConnectionHelper.DATABASE_MYSQL))
948        {
949            mapping = _mapping.get("mysql");
950        }
951        else if (_databaseType.equals(ConnectionHelper.DATABASE_ORACLE))
952        {
953            mapping = _mapping.get("oracle");
954        }
955        
956        return mapping != null ? mapping.getOrDefault(type, mapping.get("string")) : "";
957    }
958    
959    /**
960     * Execute SQL queries
961     * @throws SQLException if a sql error occurred
962     * @throws IOException if an IO error occurred
963     */
964    protected void executeQueries() throws SQLException, IOException
965    {
966        int nbTotalTable = _getNbTable();
967
968        boolean isInfoEnabled = getLogger().isInfoEnabled();
969        if (isInfoEnabled)
970        {
971            List<String> i18nParams = new ArrayList<>();
972            i18nParams.add(String.valueOf(nbTotalTable));
973            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_BEGINNING", i18nParams)));
974        }
975
976        int nbTableCreated = 0;
977        int limitPourcentReport = 10;
978        try
979        {
980            for (Entry<String, ExportTableInfo> entry : _tablesInfos.entrySet()) 
981            {
982                ExportTableInfo tableInfo = entry.getValue();
983                
984                List<String> queries = tableInfo.getCreateQueries();
985                _executeQueries(queries);
986                nbTableCreated += queries.size();
987                
988                int pourcent = nbTableCreated * 100 / nbTotalTable;
989                if (pourcent >= limitPourcentReport)
990                {
991                    if (isInfoEnabled)
992                    {
993                        List<String> i18nParams = new ArrayList<>();
994                        i18nParams.add(String.valueOf(limitPourcentReport));
995                        getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_ADVANCE", i18nParams)));
996                    }
997                    limitPourcentReport += 10;
998                }
999                
1000                _executeQueries(tableInfo.getCommentQueries());
1001                _executeQueries(tableInfo.getInsertQueries());
1002            }
1003        }
1004        catch (IOException e)
1005        {
1006            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_GET_FILE_ERROR")), e);
1007        }
1008        finally
1009        {
1010            if (isInfoEnabled)
1011            {
1012                List<String> i18nParams = new ArrayList<>();
1013                i18nParams.add(String.valueOf(nbTableCreated));
1014                getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_ANALYZE_TABLE_CREATE_FINISH", i18nParams)));
1015            }
1016        }
1017    }
1018    
1019    private int _getNbTable()
1020    {
1021        int nbTable = 0;
1022        for (Entry<String, ExportTableInfo> entry : _tablesInfos.entrySet())
1023        {
1024            ExportTableInfo tableInfo = entry.getValue();
1025            
1026            List<String> listQuery = tableInfo.getCreateQueries();
1027            nbTable += listQuery.size();
1028        }
1029        
1030        return nbTable;
1031    }
1032    
1033    /**
1034     * Execute query list
1035     * @param listQuery the list of query to execute
1036     * @throws SQLException if a sql error occurred
1037     * @throws IOException if an IO error occurred
1038     */
1039    protected void _executeQueries(List<String> listQuery) throws SQLException, IOException
1040    {
1041        for (String query : listQuery)
1042        {
1043            getLogger().debug(query);
1044
1045            PreparedStatement stmt = null;
1046            try
1047            {
1048                stmt = _connection.prepareStatement(query);
1049                stmt.execute();
1050            }
1051            catch (SQLException e)
1052            {
1053                throw new SQLException("The SQL query failed : " + query, e);
1054            }
1055            finally
1056            {
1057                // Close the connection resources
1058                ConnectionHelper.cleanup(stmt);
1059            }
1060        }
1061    }
1062    
1063    /**
1064     * Prepare INSERT statement
1065     * @param tableName the table name
1066     * @return the INSERT preparedStatement
1067     * @throws SQLException if a sql error occurred
1068     */
1069    protected PreparedStatement getInsertPreparedStatementFromTableName(String tableName) throws SQLException
1070    {
1071        StringBuilder sql = new StringBuilder();
1072        sql.append("INSERT INTO ");
1073        sql.append(_normalizeNameComponent.normalizedTableName(_sqlTablePrefix, _mappingPolicy, tableName, _connection));
1074        sql.append(" VALUES ( ?");
1075        
1076        ExportTableInfo tableInfo = _tablesInfos.get(tableName);
1077
1078        for (int i = 1; i < tableInfo.getNbColumns(); i++)
1079        {
1080            sql.append(", ?");
1081        }
1082        sql.append(")");
1083        
1084        PreparedStatement stmt = _connection.prepareStatement(sql.toString());
1085        return stmt;
1086    }
1087}