Author: sergiykarpenko Date: 2010-09-13 02:25:03 -0400 (Mon, 13 Sep 2010) New Revision: 3106 Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerException.java Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleaner.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java Log: EXOJCR-939: DBCleanerService updated Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleaner.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleaner.java 2010-09-10 17:05:02 UTC (rev 3105) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleaner.java 2010-09-13 06:25:03 UTC (rev 3106) @@ -17,7 +17,13 @@ package org.exoplatform.services.jcr.impl.util.jdbc; import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Statement; +import org.exoplatform.services.log.ExoLogger; +import org.exoplatform.services.log.Log; + /** * The goal of this class is remove workspace data from database. * Created by The eXo Platform SAS. @@ -30,6 +36,8 @@ public class DBCleaner { + protected final static Log LOG = ExoLogger.getLogger("exo.jcr.component.core.DBCleaner"); + protected String REMOVE_ITEMS; protected String REMOVE_VALUES; @@ -46,50 +54,175 @@ private final String containerName; + private final boolean isMultiDB; + /** * Constructor. * * @param containerName - workspace name * @param connection - SQL conneciton */ - public DBCleaner(Connection connection, String containerName) + public DBCleaner(Connection connection, String containerName, boolean isMulti) { this.connection = connection; this.containerName = containerName; + this.isMultiDB = isMulti; prepareQueries(); } - protected void prepareQueries() + /** + * Remove workspace data from database. + * <ul> + * <li>If workspace uses multiDB data source - tables associated with this workspace + * will be dropped. + * <li>If workspace uses singleDB data source - all records of this workspace will + * be removed. + * </ul> + * + * <p>Connection used by this method will be closed at final. + * + * @throws DBCleanerException - if exception during data cleanup occures. + */ + public void cleanWorkspace() throws DBCleanerException { - + try + { + // check is multi db + if (isMultiDB) + { + //remove table + dropWorkspace(); + } + else + { + // clean up all record of this container + removeWorkspaceRecords(); + } + connection.commit(); + } + catch (SQLException e) + { + + // TODO do we need rollback here? + try + { + connection.rollback(); + } + catch (SQLException rollbackException) + { + LOG.error("Can not rollback changes after exception " + e.getMessage(), rollbackException); + } + throw new DBCleanerException(e.getMessage(), e); + } + finally + { + try + { + connection.close(); + } + catch (SQLException e) + { + LOG.error("Error of a connection closing. " + e, e); + } + } } - public void removeWorkspace() + protected void prepareQueries() { - - } - - public void cleanupWorkspace() - { //for single db support REMOVE_ITEMS = "delete from JCR_SITEM where CONTAINER_NAME=?"; - REMOVE_VALUES = "delete from JCR_SVALUE where PROPERTY_ID=?"; - REMOVE_REFERENCES = "delete from JCR_SREF where PROPERTY_ID=?"; + REMOVE_VALUES = + "delete from JCR_SVALUE V where exists " + + "( select * from JCR_SITEM I where I.ID=V.PROPERTY_ID and I.CONTAINER_NAME=? )"; + //TODO R.PROPERTY_ID or R.NODE_ID? + REMOVE_REFERENCES = + "delete from JCR_SREF R where exists " + + "( select * from JCR_SITEM I where I.ID=R.PROPERTY_ID and I.CONTAINER_NAME=? )"; + // for multi db support + //TODO do we need remove indexes? + //different databases may be configured to use different indexes DROP_JCR_MITEM_TABLE = "DROP TABLE JCR_MITEM"; DROP_JCR_MVALUE_TABLE = "DROP TABLE JCR_MVALUE"; DROP_MREF_TABLE = "DROP TABLE JCR_MREF"; + } - // DROP TABLE orders; - // DROP DATABASE mydatabase; - // DROP VIEW viewname; - // DROP INDEX orders.indexname; - // - // -- FOR USE WITH ALTER COMMANDS - // DROP COLUMN column_name - // DROP FOREIGN KEY (foreign_key_name) + protected void dropWorkspace() throws SQLException + { + final Statement statement = connection.createStatement(); + connection.setAutoCommit(false); + + try + { + // order of dropped tables is important + statement.executeUpdate(DROP_MREF_TABLE); + statement.executeUpdate(DROP_JCR_MVALUE_TABLE); + statement.executeUpdate(DROP_JCR_MITEM_TABLE); + } + finally + { + if (statement != null) + { + try + { + statement.close(); + } + catch (SQLException e) + { + LOG.error("Can't close the Statement: " + e); + } + } + } } + protected void removeWorkspaceRecords() throws SQLException + { + PreparedStatement statements = null; + try + { + statements = connection.prepareStatement(REMOVE_REFERENCES); + statements.setString(1, containerName); + statements.executeUpdate(); + } + finally + { + if (statements != null) + { + statements.close(); + statements = null; + } + } + + try + { + statements = connection.prepareStatement(REMOVE_VALUES); + statements.setString(1, containerName); + statements.executeUpdate(); + } + finally + { + if (statements != null) + { + statements.close(); + statements = null; + } + } + + try + { + statements = connection.prepareStatement(REMOVE_ITEMS); + statements.setString(1, containerName); + statements.executeUpdate(); + } + finally + { + if (statements != null) + { + statements.close(); + statements = null; + } + } + } } Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerException.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerException.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerException.java 2010-09-13 06:25:03 UTC (rev 3106) @@ -0,0 +1,9 @@ +package org.exoplatform.services.jcr.impl.util.jdbc; + +public class DBCleanerException extends Exception +{ + public DBCleanerException(String message, Throwable e) + { + super(message, e); + } +} Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java 2010-09-10 17:05:02 UTC (rev 3105) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java 2010-09-13 06:25:03 UTC (rev 3106) @@ -16,10 +16,6 @@ */ package org.exoplatform.services.jcr.impl.util.jdbc; -import org.exoplatform.services.jcr.config.RepositoryConfigurationException; -import org.exoplatform.services.jcr.config.RepositoryEntry; -import org.exoplatform.services.jcr.config.WorkspaceEntry; - import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; @@ -30,6 +26,12 @@ import javax.naming.NamingException; import javax.sql.DataSource; +import org.exoplatform.services.jcr.config.RepositoryConfigurationException; +import org.exoplatform.services.jcr.config.RepositoryEntry; +import org.exoplatform.services.jcr.config.WorkspaceEntry; +import org.exoplatform.services.log.ExoLogger; +import org.exoplatform.services.log.Log; + /** * Created by The eXo Platform SAS. * @@ -40,6 +42,7 @@ */ public class DBCleanerService { + protected final static Log LOG = ExoLogger.getLogger("exo.jcr.component.core.DBCleanerService"); public static void removeWorkspaceData(WorkspaceEntry wsConfig) throws RepositoryConfigurationException, NamingException, RepositoryException, IOException @@ -54,7 +57,6 @@ ds != null ? ds.getConnection() : (wsJDBCConfig.getDbUserName() != null ? DriverManager.getConnection( wsJDBCConfig.getDbUrl(), wsJDBCConfig.getDbUserName(), wsJDBCConfig.getDbPassword()) : DriverManager .getConnection(wsJDBCConfig.getDbUrl())); - } catch (SQLException e) { @@ -64,18 +66,13 @@ throw new RepositoryException(err, e); } - DBCleaner cleaner = new DBCleaner(conn, wsJDBCConfig.getContainerName()); - // check is multi db - if (wsJDBCConfig.isMultiDb()) - { - //remove table - cleaner.removeWorkspace(); + DBCleaner cleaner = new DBCleaner(conn, wsJDBCConfig.getContainerName(), wsJDBCConfig.isMultiDb()); + + try{ + cleaner.cleanWorkspace(); + }catch(DBCleanerException e){ + throw new RepositoryException(e.getMessage(),e); } - else - { - // clean up all record of this container - cleaner.cleanupWorkspace(); - } } public static void removeRepositoryData(RepositoryEntry repoConfig) throws RepositoryConfigurationException,