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.sql.Connection;
019import java.sql.PreparedStatement;
020import java.sql.ResultSet;
021import java.sql.SQLException;
022import java.util.ArrayList;
023import java.util.List;
024
025import org.apache.avalon.framework.component.Component;
026import org.apache.avalon.framework.service.ServiceException;
027import org.apache.avalon.framework.service.ServiceManager;
028import org.apache.avalon.framework.service.Serviceable;
029import org.apache.commons.lang.StringUtils;
030
031import org.ametys.core.datasource.ConnectionHelper;
032import org.ametys.core.util.I18nUtils;
033import org.ametys.runtime.config.Config;
034import org.ametys.runtime.i18n.I18nizableText;
035import org.ametys.runtime.plugin.component.AbstractLogEnabled;
036
037/**
038 * Delete existing table for export
039 */
040public class DeleteSqlTableComponent extends AbstractLogEnabled implements Component, Serviceable
041{
042    /** The component role */
043    public static final String ROLE = DeleteSqlTableComponent.class.getName();
044
045    /** The i18n translator. */
046    protected I18nUtils _i18nTranslator;
047    
048    public void service(ServiceManager manager) throws ServiceException
049    {
050        _i18nTranslator = (I18nUtils) manager.lookup(I18nUtils.ROLE);
051    }
052    
053    /**
054     * Delete all tables of a preceding content export
055     * @param exportConfiguration the content export configuration
056     * @throws SQLException if an error occurred while deleting tables
057     */
058    public void deleteExistingSqlTables(ExportConfiguration exportConfiguration) throws SQLException
059    {
060        String tablePrefix = exportConfiguration.getTablePrefix();
061        
062        Connection connection = null;
063        ResultSet rs = null;
064        try
065        {
066            String datasourceId = Config.getInstance().getValue("org.ametys.plugins.contentio.content.export.datasource");
067            connection = ConnectionHelper.getConnection(datasourceId);
068            String datatype = ConnectionHelper.getDatabaseType(connection);
069            
070            if (datatype.equals(ConnectionHelper.DATABASE_ORACLE))
071            {
072                tablePrefix = tablePrefix.toUpperCase();
073            }
074            
075            // Request to select all table whose name begins by '_sqlTablePrefix'
076            String[] tableType = {"TABLE"};
077            rs = connection.getMetaData().getTables(connection.getCatalog(), connection.getSchema(), tablePrefix + "%", tableType);
078            
079            if (datatype.equals(ConnectionHelper.DATABASE_MYSQL))
080            {
081                _deleteMysqlTable(rs, connection);
082            }
083            else if (datatype.equals(ConnectionHelper.DATABASE_ORACLE))
084            {
085                _deleteOracleTable(rs, connection);
086            }
087        }
088        catch (Exception e)
089        {
090            getLogger().error(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_DELETE_ERROR_SQL")), e);
091        }
092        finally
093        {
094            // Close the connection resources
095            ConnectionHelper.cleanup(rs);
096            ConnectionHelper.cleanup(connection);
097        }
098    }
099    
100    private void _deleteMysqlTable(ResultSet rs, Connection connection) throws SQLException
101    {
102        StringBuilder dropRequest = new StringBuilder();
103        int nbTable = 0;
104        if (rs.next())
105        {
106            dropRequest.append("DROP TABLE ");
107            dropRequest.append(rs.getString("table_name")); 
108            nbTable++;
109            while (rs.next()) 
110            {
111                dropRequest.append(", ");
112                dropRequest.append(rs.getString("table_name")); 
113                nbTable++;
114            }
115        }
116        
117        if (getLogger().isInfoEnabled())
118        {
119            List<String> i18nParams = new ArrayList<>();
120            i18nParams.add(String.valueOf(nbTable));
121            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_DELETE_NB_TABLE", i18nParams)));
122        }
123        
124        PreparedStatement stmt = null;
125        try 
126        {
127            if (StringUtils.isNotEmpty(dropRequest.toString()))
128            {
129                stmt = connection.prepareStatement("SET FOREIGN_KEY_CHECKS = 0");
130                stmt.execute();
131                ConnectionHelper.cleanup(stmt);
132                
133                stmt = connection.prepareStatement(dropRequest.toString());
134                stmt.execute();
135                ConnectionHelper.cleanup(stmt);
136                
137                stmt = connection.prepareStatement("SET FOREIGN_KEY_CHECKS = 1");
138                stmt.execute();
139            }
140        }
141        finally
142        {
143            ConnectionHelper.cleanup(stmt);
144        }
145
146        if (getLogger().isInfoEnabled())
147        {
148            List<String> i18nParams = new ArrayList<>();
149            i18nParams.add(String.valueOf(nbTable));
150            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_DELETE_FINISH", i18nParams)));
151        }
152    }
153    
154    private void _deleteOracleTable(ResultSet rs, Connection connection) throws SQLException
155    {
156        PreparedStatement stmt = null;
157        try
158        {
159            stmt = connection.prepareStatement("ALTER SESSION SET RECYCLEBIN=OFF");
160            stmt.execute();
161        }
162        finally
163        {
164            ConnectionHelper.cleanup(stmt);
165        }
166        
167        StringBuilder dropRequest = new StringBuilder();
168        List<String> listRequest = new ArrayList<>();
169
170        if (rs.next())
171        {
172            dropRequest.append("DROP TABLE ");
173            dropRequest.append(rs.getString("table_name"));
174            dropRequest.append(" CASCADE CONSTRAINTS");
175            listRequest.add(dropRequest.toString());
176            
177            while (rs.next()) 
178            {
179                dropRequest = new StringBuilder();
180                
181                dropRequest.append("DROP TABLE ");
182                dropRequest.append(rs.getString("table_name"));
183                dropRequest.append(" CASCADE CONSTRAINTS");
184                listRequest.add(dropRequest.toString());
185            }
186            
187        }
188        
189        int nbTable = listRequest.size();
190        
191        boolean isInfoEnabled = getLogger().isInfoEnabled();
192        boolean isWarnEnabled = getLogger().isWarnEnabled();
193        
194        if (isInfoEnabled)
195        {
196            List<String> i18nParams = new ArrayList<>();
197            i18nParams.add(String.valueOf(nbTable));
198            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_DELETE_NB_TABLE_ORACLE", i18nParams)));
199        }
200        
201        int nbTableDeleted = 0;
202        int pourcentLimit = 10;
203        for (String request : listRequest)
204        {
205            getLogger().debug(request);
206            
207            try 
208            {
209                stmt = connection.prepareStatement(request);
210                stmt.execute();
211                nbTableDeleted++;
212            }
213            catch (Exception e)
214            {
215                if (isWarnEnabled)
216                {
217                    List<String> i18nParams = new ArrayList<>();
218                    i18nParams.add(String.valueOf(request));
219                    getLogger().warn(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_DELETE_ERROR_TABLE_ORACLE", i18nParams)), e);
220                }
221            }
222            finally
223            {
224                ConnectionHelper.cleanup(stmt);
225            }
226
227            int pourcent = nbTableDeleted * 100 / nbTable;
228            if (pourcent >= pourcentLimit)
229            {
230                if (isInfoEnabled)
231                {
232                    List<String> i18nParams = new ArrayList<>();
233                    i18nParams.add(String.valueOf(pourcentLimit));
234                    getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_DELETE_ADVANCE_ORACLE", i18nParams)));
235                }
236                pourcentLimit += 10;
237            }
238        }
239        
240        if (isInfoEnabled)
241        {
242            List<String> i18nParams = new ArrayList<>();
243            i18nParams.add(String.valueOf(nbTableDeleted));
244            getLogger().info(_i18nTranslator.translate(new I18nizableText("plugin.contentio", "PLUGINS_CONTENTIO_CONTENT_EXPORT_LOG_DELETE_FINISH", i18nParams)));
245        }
246    }
247
248}