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