Author: sergiykarpenko Date: 2010-09-16 10:44:28 -0400 (Thu, 16 Sep 2010) New Revision: 3122 Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/MultiDBCleaner.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/SingleDBCleaner.java jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sql 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: DBCleaner separated to multi and single; script support added 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-16 13:41:50 UTC (rev 3121) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleaner.java 2010-09-16 14:44:28 UTC (rev 3122) @@ -16,14 +16,23 @@ */ package org.exoplatform.services.jcr.impl.util.jdbc; +import org.exoplatform.services.jcr.core.security.JCRRuntimePermissions; +import org.exoplatform.services.jcr.impl.util.SecurityHelper; import org.exoplatform.services.log.ExoLogger; import org.exoplatform.services.log.Log; +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.security.AccessController; +import java.security.PrivilegedAction; +import java.security.PrivilegedExceptionAction; import java.sql.Connection; -import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.util.regex.Matcher; +import java.util.regex.Pattern; /** * The goal of this class is remove workspace data from database. @@ -34,47 +43,29 @@ * @author <a href=&quot;karpenko.sergiy(a)gmail.com&quot;&gt;Karpenko Sergiy</a> * @version $Id: DBCleaner.java 111 2008-11-11 11:11:11Z serg $ */ -public class DBCleaner +public abstract class DBCleaner { protected final static Log LOG = ExoLogger.getLogger("exo.jcr.component.core.DBCleaner"); - protected final static int MAX_IDS_RETURNED = 100; + protected final Pattern dbObjectNamePattern; - protected String REMOVE_PROPERTIES; + protected final Connection connection; - protected String REMOVE_ITEMS; + protected String[] scripts; - protected String REMOVE_VALUES; - - protected String REMOVE_REFERENCES; - - protected String GET_CHILD_IDS; - - protected String DROP_JCR_MITEM_TABLE; - - protected String DROP_JCR_MVALUE_TABLE; - - protected String DROP_MREF_TABLE; - - private final Connection connection; - - private final String containerName; - - private final boolean isMultiDB; - /** * Constructor. * * @param containerName - workspace name * @param connection - SQL conneciton */ - public DBCleaner(Connection connection, String containerName, boolean isMulti) + public DBCleaner(Connection connection, InputStream inputStream) throws IOException { + this.dbObjectNamePattern = Pattern.compile(DBInitializer.SQL_OBJECTNAME, Pattern.CASE_INSENSITIVE); this.connection = connection; - this.containerName = containerName; - this.isMultiDB = isMulti; - prepareQueries(); + // parse script + this.scripts = readScriptResource(inputStream); } /** @@ -92,20 +83,37 @@ */ public void cleanWorkspace() throws DBCleanerException { + SecurityManager security = System.getSecurityManager(); + if (security != null) + { + security.checkPermission(JCRRuntimePermissions.MANAGE_REPOSITORY_PERMISSION); + } + + String sql = null; + Statement st = null; try { connection.setAutoCommit(false); - // check is multi db - if (isMultiDB) + st = connection.createStatement(); + for (String scr : scripts) { - //remove table - dropWorkspace(); + String s = cleanWhitespaces(scr.trim()); + if (s.length() > 0) + { + if (!canExecuteQuery(connection, sql = s)) + { + // table not found , so try drop other + continue; + } + + if (LOG.isDebugEnabled()) + { + LOG.debug("Execute script: \n[" + sql + "]"); + } + executeQuery(st, sql); + } } - else - { - // clean up all record of this container - removeWorkspaceRecords(); - } + connection.commit(); } catch (SQLException e) @@ -122,6 +130,18 @@ } finally { + if (st != null) + { + try + { + st.close(); + } + catch (SQLException e) + { + LOG.error("Can't close the Statement: " + e); + } + } + try { connection.close(); @@ -133,138 +153,145 @@ } } - protected void prepareQueries() + protected boolean canExecuteQuery(Connection conn, String sql) throws SQLException { - //for single db support - - REMOVE_PROPERTIES = "delete from JCR_SITEM where I_CLASS=2 and CONTAINER_NAME=?"; - - GET_CHILD_IDS = - "select ID from JCR_SITEM where CONTAINER_NAME=? and ID not in(select PARENT_ID from JCR_SITEM where CONTAINER_NAME=?)"; - - // "delete from JCR_SITEM where CONTAINER_NAME=?"; - REMOVE_ITEMS = "delete from JCR_SITEM where ID in( ? )"; - - REMOVE_VALUES = - "delete from JCR_SVALUE where exists" - + "(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?)"; - - REMOVE_REFERENCES = - "delete from JCR_SREF where exists" - + "(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?)"; - - // for multi db support - DROP_JCR_MITEM_TABLE = "DROP TABLE JCR_MITEM"; - DROP_JCR_MVALUE_TABLE = "DROP TABLE JCR_MVALUE"; - DROP_MREF_TABLE = "DROP TABLE JCR_MREF"; + return isTablesFromQueryExists(conn, sql); } - protected void dropWorkspace() throws SQLException + protected String cleanWhitespaces(String string) { - final Statement statement = connection.createStatement(); - connection.setAutoCommit(false); - - try + if (string != null) { - // 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) + char[] cc = string.toCharArray(); + for (int ci = cc.length - 1; ci > 0; ci--) { - try + if (Character.isWhitespace(cc[ci])) { - statement.close(); + cc[ci] = ' '; } - catch (SQLException e) - { - LOG.error("Can't close the Statement: " + e); - } } + return new String(cc); } + return string; } - protected void removeWorkspaceRecords() throws SQLException + protected void executeQuery(final Statement statement, final String sql) throws SQLException { - executeUpdate(connection, REMOVE_REFERENCES, containerName); - executeUpdate(connection, REMOVE_VALUES, containerName); - - clearItems(connection, containerName); + SecurityHelper.doPriviledgedSQLExceptionAction(new PrivilegedExceptionAction<Object>() + { + public Object run() throws Exception + { + statement.executeUpdate(sql); + return null; + } + }); } - protected void executeUpdate(Connection connection, String query, String containerName) throws SQLException + protected boolean isTableExists(Connection conn, String tableName) throws SQLException { - PreparedStatement statements = null; + ResultSet trs = conn.getMetaData().getTables(null, null, tableName, null); try { - statements = connection.prepareStatement(query); - statements.setString(1, containerName); - statements.executeUpdate(); + boolean res = false; + while (trs.next()) + { + res = true; // check for columns/table type matching etc. + } + return res; } finally { - if (statements != null) + try { - statements.close(); - statements = null; + trs.close(); } + catch (SQLException e) + { + LOG.error("Can't close the ResultSet: " + e); + } } } - protected void clearItems(Connection connection, String containerName) throws SQLException + protected boolean isTablesFromQueryExists(Connection conn, String sql) throws SQLException { - executeUpdate(connection, REMOVE_PROPERTIES, containerName); + Matcher tMatcher = dbObjectNamePattern.matcher(sql); + while (tMatcher.find()) + { + // got table name + String tableName = sql.substring(tMatcher.start(), tMatcher.end()); + if (!isTableExists(conn, tableName)) + { + LOG.error("Table [" + tableName + "] from query [" + sql + + "] was not found. So query will not be executed , but will try execute next one."); + return false; + } + } + return true; + } - // Remove only child nodes in cycle, till all nodes will be removed. - // Such algorithm used to avoid any constraint violation exception related to foreign key. - PreparedStatement getChildItems = null; - Statement removeItems = connection.createStatement(); + protected String[] readScriptResource(final InputStream is) throws IOException + { + //extract string + PrivilegedAction<InputStreamReader> actionGetReader = new PrivilegedAction<InputStreamReader>() + { + public InputStreamReader run() + { + return new InputStreamReader(is); + } + }; + InputStreamReader isr = AccessController.doPrivileged(actionGetReader); + String script = null; try { - getChildItems = connection.prepareStatement(GET_CHILD_IDS); - getChildItems.setString(1, containerName); - getChildItems.setString(2, containerName); - - getChildItems.setMaxRows(MAX_IDS_RETURNED); - - do + StringBuilder sbuff = new StringBuilder(); + char[] buff = new char[is.available()]; + int r = 0; + while ((r = isr.read(buff)) > 0) { - ResultSet result = getChildItems.executeQuery(); - StringBuilder childListBuilder = new StringBuilder(); - if (result.next()) - { - childListBuilder.append("'" + result.getString(1) + "'"); - } - else - { - break; - } - while (result.next()) - { - childListBuilder.append(" , '" + result.getString(1) + "'"); - } - // now remove nodes; - String q = REMOVE_ITEMS.replace("?", childListBuilder.toString()); - removeItems.executeUpdate(q); + sbuff.append(buff, 0, r); } - while (true); + + script = sbuff.toString(); } finally { - if (getChildItems != null) + try { - getChildItems.close(); - getChildItems = null; + is.close(); } - if (removeItems != null) + catch (IOException e) { - removeItems.close(); - removeItems = null; } } + + // parse scripts + String[] scripts = null; + if (script.startsWith(DBInitializer.SQL_DELIMITER_COMMENT_PREFIX)) + { + // read custom prefix + try + { + String s = script.substring(DBInitializer.SQL_DELIMITER_COMMENT_PREFIX.length()); + int endOfDelimIndex = s.indexOf("*/"); + String delim = s.substring(0, endOfDelimIndex).trim(); + s = s.substring(endOfDelimIndex + 2).trim(); + scripts = s.split(delim); + } + catch (IndexOutOfBoundsException e) + { + LOG.warn("Error of parse SQL-script file. Invalid DELIMITER configuration. Valid format is '" + + DBInitializer.SQL_DELIMITER_COMMENT_PREFIX + + "XXX*/' at begin of the SQL-script file, where XXX - DELIMITER string." + " Spaces will be trimed. ", + e); + LOG.info("Using DELIMITER:[" + DBInitializer.SQL_DELIMITER + "]"); + scripts = script.split(DBInitializer.SQL_DELIMITER); + } + } + else + { + scripts = script.split(DBInitializer.SQL_DELIMITER); + } + return scripts; } } 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-16 13:41:50 UTC (rev 3121) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java 2010-09-16 14:44:28 UTC (rev 3122) @@ -16,7 +16,16 @@ */ 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 org.exoplatform.services.log.ExoLogger; +import org.exoplatform.services.log.Log; + import java.io.IOException; +import java.io.InputStream; +import java.security.AccessController; +import java.security.PrivilegedAction; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; @@ -26,12 +35,6 @@ 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. * @@ -66,13 +69,34 @@ throw new RepositoryException(err, e); } - DBCleaner cleaner = new DBCleaner(conn, wsJDBCConfig.getContainerName(), wsJDBCConfig.isMultiDb()); + final String sqlPath = "/conf/storage/cleanup/jcr-" + (wsJDBCConfig.isMultiDb() ? "m" : "s") + "jdbc.sql"; + PrivilegedAction<InputStream> action = new PrivilegedAction<InputStream>() + { + public InputStream run() + { + return this.getClass().getResourceAsStream(sqlPath); + } + }; + InputStream is = AccessController.doPrivileged(action); - try{ - cleaner.cleanWorkspace(); - }catch(DBCleanerException e){ - throw new RepositoryException(e.getMessage(),e); + DBCleaner cleaner; + if (wsJDBCConfig.isMultiDb()) + { + cleaner = new MultiDBCleaner(conn, is); } + else + { + cleaner = new SingleDBCleaner(conn, is, wsJDBCConfig.getContainerName()); + } + + try + { + cleaner.cleanWorkspace(); + } + catch (DBCleanerException e) + { + throw new RepositoryException(e.getMessage(), e); + } } public static void removeRepositoryData(RepositoryEntry repoConfig) throws RepositoryConfigurationException, Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/MultiDBCleaner.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/MultiDBCleaner.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/MultiDBCleaner.java 2010-09-16 14:44:28 UTC (rev 3122) @@ -0,0 +1,37 @@ +/* + * Copyright (C) 2003-2010 eXo Platform SAS. + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Affero General Public License + * as published by the Free Software Foundation; either version 3 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, see<http://www.gnu.org/licenses/>;. + */ +package org.exoplatform.services.jcr.impl.util.jdbc; + +import java.io.IOException; +import java.io.InputStream; +import java.sql.Connection; + +/** + * Created by The eXo Platform SAS. + * + * <br/>Date: + * + * @author <a href=&quot;karpenko.sergiy(a)gmail.com&quot;&gt;Karpenko Sergiy</a> + * @version $Id: MultiDBCLeaner.java 111 2008-11-11 11:11:11Z serg $ + */ +public final class MultiDBCleaner extends DBCleaner +{ + public MultiDBCleaner(Connection connection, InputStream inputStream) throws IOException + { + super(connection, inputStream); + } +} Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/SingleDBCleaner.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/SingleDBCleaner.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/SingleDBCleaner.java 2010-09-16 14:44:28 UTC (rev 3122) @@ -0,0 +1,145 @@ +/* + * Copyright (C) 2003-2010 eXo Platform SAS. + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Affero General Public License + * as published by the Free Software Foundation; either version 3 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, see<http://www.gnu.org/licenses/>;. + */ +package org.exoplatform.services.jcr.impl.util.jdbc; + +import java.io.IOException; +import java.io.InputStream; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +/** + * Created by The eXo Platform SAS. + * + * <br/>Date: + * + * @author <a href=&quot;karpenko.sergiy(a)gmail.com&quot;&gt;Karpenko Sergiy</a> + * @version $Id: SingleDBCleaner.java 111 2008-11-11 11:11:11Z serg $ + */ +public final class SingleDBCleaner extends DBCleaner +{ + public final String CLEAN_JCR_SITEM_AS_DEFAULT = "/*$CLEAN_JCR_SITEM_DEFAULT*/"; + + protected final int MAX_IDS_RETURNED = 100; + + protected String GET_CHILD_IDS; + + protected String REMOVE_ITEMS; + + protected final String containerName; + + public SingleDBCleaner(Connection connection, InputStream inputStream, String containerName) throws IOException + { + super(connection, inputStream); + this.containerName = containerName; + prepareQueries(); + } + + protected void prepareQueries() + { + GET_CHILD_IDS = + "select ID from JCR_SITEM where CONTAINER_NAME=? and ID not in(select PARENT_ID from JCR_SITEM where CONTAINER_NAME=?)"; + + REMOVE_ITEMS = "delete from JCR_SITEM where ID in( ? )"; + } + + /** + * {@inheritDoc} + */ + protected boolean canExecuteQuery(Connection conn, String sql) throws SQLException + { + if (sql.equalsIgnoreCase(CLEAN_JCR_SITEM_AS_DEFAULT)) + { + return true; + } + else + { + return isTablesFromQueryExists(conn, sql); + } + } + + /** + * {@inheritDoc} + */ + protected void executeQuery(final Statement statement, final String sql) throws SQLException + { + if (sql.equalsIgnoreCase(CLEAN_JCR_SITEM_AS_DEFAULT)) + { + clearItems(statement.getConnection(), containerName); + } + else + { + // check query for "?" mask and replace it with containerName + String q = sql.replace("?", "'" + containerName + "'"); + super.executeQuery(statement, q); + } + } + + private void clearItems(Connection connection, String containerName) throws SQLException + { + // Remove only child nodes in cycle, till all nodes will be removed. + // Such algorithm used to avoid any constraint violation exception related to foreign key. + PreparedStatement getChildItems = null; + Statement removeItems = connection.createStatement(); + + try + { + getChildItems = connection.prepareStatement(GET_CHILD_IDS); + getChildItems.setString(1, containerName); + getChildItems.setString(2, containerName); + + getChildItems.setMaxRows(MAX_IDS_RETURNED); + + do + { + ResultSet result = getChildItems.executeQuery(); + StringBuilder childListBuilder = new StringBuilder(); + if (result.next()) + { + childListBuilder.append("'" + result.getString(1) + "'"); + } + else + { + break; + } + while (result.next()) + { + childListBuilder.append(" , '" + result.getString(1) + "'"); + } + // now remove nodes; + String q = REMOVE_ITEMS.replace("?", childListBuilder.toString()); + removeItems.executeUpdate(q); + } + while (true); + } + finally + { + if (getChildItems != null) + { + getChildItems.close(); + getChildItems = null; + } + if (removeItems != null) + { + removeItems.close(); + removeItems = null; + } + } + } +} Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sql 2010-09-16 14:44:28 UTC (rev 3122) @@ -0,0 +1,3 @@ +DROP TABLE JCR_MREF; +DROP TABLE JCR_MVALUE; +DROP TABLE JCR_MITEM; \ No newline at end of file Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sql 2010-09-16 14:44:28 UTC (rev 3122) @@ -0,0 +1,4 @@ +delete from JCR_SVALUE where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SREF where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SITEM where I_CLASS=2 and CONTAINER_NAME=?; +/*$CLEAN_JCR_SITEM_DEFAULT*/;