Author: sergiykarpenko Date: 2010-09-17 05:45:55 -0400 (Fri, 17 Sep 2010) New Revision: 3131 Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/IngresSQLDBCleaner.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/OracleDBCleaner.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/PgSQLDBCleaner.java Removed: 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 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: Make DBCLeaner extenders for Oracle PgSQL and Ingress dialects. 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-17 08:39:43 UTC (rev 3130) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleaner.java 2010-09-17 09:45:55 UTC (rev 3131) @@ -28,6 +28,7 @@ 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; @@ -43,31 +44,57 @@ * @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 abstract class DBCleaner +public class DBCleaner { + public final String CLEAN_JCR_SITEM_AS_DEFAULT = "/*$CLEAN_JCR_SITEM_DEFAULT*/"; protected final static Log LOG = ExoLogger.getLogger("exo.jcr.component.core.DBCleaner"); + protected final int MAX_IDS_RETURNED = 100; + + protected String GET_CHILD_IDS; + + protected String REMOVE_ITEMS; + + protected final String containerName; + protected final Pattern dbObjectNamePattern; protected final Connection connection; protected String[] scripts; + protected final boolean isMultiDB; + /** * Constructor. * - * @param containerName - workspace name - * @param connection - SQL conneciton + * @param containerName - container name (a.k.a workspace name) + * @param connection - connection to database where workspace tables is placed + * @param inputStream - inputStream from script file + * @param isMultiDB - isMultiDB + * @throws IOException - if exception occures on parsing script file input stream */ - public DBCleaner(Connection connection, InputStream inputStream) throws IOException + public DBCleaner(String containerName, Connection connection, InputStream inputStream, boolean isMultiDB) + throws IOException { this.dbObjectNamePattern = Pattern.compile(DBInitializer.SQL_OBJECTNAME, Pattern.CASE_INSENSITIVE); this.connection = connection; // parse script this.scripts = readScriptResource(inputStream); + this.containerName = containerName; + this.isMultiDB = isMultiDB; + 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( ? )"; + } + /** * Remove workspace data from database. * <ul> @@ -102,7 +129,7 @@ { if (!canExecuteQuery(connection, sql = s)) { - // table not found , so try drop other + // table from query not found , so try drop other continue; } @@ -153,11 +180,121 @@ } } + /** + * The default implementation of remove rows from JCR_SITEM table. + * Some database do not support cascade delete, or need special sittings, so + * query "delete from JCR_SITEM where CONTAINER_NAME=?" may cause constraint violation exception. + * This method takes a leafs (child nodes/properties) and remove them till, no one record is left. + * + * @throws SQLException - SQL exception. + */ + protected void clearItemsByDefault(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; + final 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 + { + final PreparedStatement getChildIds = getChildItems; + ResultSet result = + (ResultSet)SecurityHelper.doPriviledgedSQLExceptionAction(new PrivilegedExceptionAction<Object>() + { + public Object run() throws Exception + { + return getChildIds.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; + final String q = REMOVE_ITEMS.replace("?", childListBuilder.toString()); + SecurityHelper.doPriviledgedSQLExceptionAction(new PrivilegedExceptionAction<Object>() + { + public Object run() throws Exception + { + removeItems.executeUpdate(q); + return null; + } + }); + } + while (true); + } + finally + { + if (getChildItems != null) + { + getChildItems.close(); + getChildItems = null; + } + if (removeItems != null) + { + removeItems.close(); + } + } + } + + /** + * Check can we safely execute query. + * If tables used in query does not exists ,we can not execute query. + */ protected boolean canExecuteQuery(Connection conn, String sql) throws SQLException { - return isTablesFromQueryExists(conn, sql); + if (!isMultiDB && sql.equalsIgnoreCase(CLEAN_JCR_SITEM_AS_DEFAULT)) + { + // check queries used in clearItemsByDefault + if (!canExecuteQuery(conn, GET_CHILD_IDS)) + { + return false; + } + if (!canExecuteQuery(conn, REMOVE_ITEMS)) + { + return false; + } + return true; + } + else + { + 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; + } } + /** + * Cleans redundant whitespaces from query. + */ protected String cleanWhitespaces(String string) { if (string != null) @@ -175,16 +312,29 @@ return string; } - protected void executeQuery(final Statement statement, final String sql) throws SQLException + /** + * Execute query. + */ + protected void executeQuery(final Statement statement, String sql) throws SQLException { - SecurityHelper.doPriviledgedSQLExceptionAction(new PrivilegedExceptionAction<Object>() + if (!isMultiDB && sql.equalsIgnoreCase(CLEAN_JCR_SITEM_AS_DEFAULT)) { - public Object run() throws Exception + clearItemsByDefault(statement.getConnection(), containerName); + } + else + { + // in case of singleDB query - check query for "?" mask and replace it with containerName + final String q = (containerName != null) ? sql.replace("?", "'" + containerName + "'") : sql; + //super.executeQuery(statement, q); + SecurityHelper.doPriviledgedSQLExceptionAction(new PrivilegedExceptionAction<Object>() { - statement.executeUpdate(sql); - return null; - } - }); + public Object run() throws Exception + { + statement.executeUpdate(q); + return null; + } + }); + } } protected boolean isTableExists(Connection conn, String tableName) throws SQLException @@ -212,23 +362,9 @@ } } - protected boolean isTablesFromQueryExists(Connection conn, String sql) throws SQLException - { - 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; - } - + /** + * Extracts SQL queries from script file input stream. + */ protected String[] readScriptResource(final InputStream is) throws IOException { //extract string 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-17 08:39:43 UTC (rev 3130) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java 2010-09-17 09:45:55 UTC (rev 3131) @@ -37,6 +37,8 @@ import javax.sql.DataSource; /** + * DBCleanerService deliver tools for clean workspace or repository data from database. + * * Created by The eXo Platform SAS. * * <br/>Date: @@ -48,6 +50,16 @@ { protected final static Log LOG = ExoLogger.getLogger("exo.jcr.component.core.DBCleanerService"); + /** + * Remove workspace data. + * Tables will be removed in case of multiDB, or only record will be removed in case of singleDb. + * + * @param wsConfig - workspace configuration. + * @throws RepositoryConfigurationException - exception on parsing workspace configuration + * @throws NamingException - exception on parsing workspace configuration or getting DataSource + * @throws RepositoryException - exception on parsing workspace configuration or data cleanup + * @throws IOException - exception on parsing workspace configuration + */ public static void removeWorkspaceData(WorkspaceEntry wsConfig) throws RepositoryConfigurationException, NamingException, RepositoryException, IOException { @@ -70,7 +82,8 @@ throw new RepositoryException(err, e); } - final String sqlPath = getScriptPath(wsJDBCConfig.getDbDialect(), wsJDBCConfig.isMultiDb()); + String dbDialect = wsJDBCConfig.getDbDialect(); + final String sqlPath = getScriptPath(dbDialect, wsJDBCConfig.isMultiDb()); PrivilegedAction<InputStream> action = new PrivilegedAction<InputStream>() { public InputStream run() @@ -81,15 +94,30 @@ InputStream is = AccessController.doPrivileged(action); DBCleaner cleaner; - if (wsJDBCConfig.isMultiDb()) + if (dbDialect == DBConstants.DB_DIALECT_ORACLEOCI) { - cleaner = new MultiDBCleaner(conn, is); + LOG.warn(DBConstants.DB_DIALECT_ORACLEOCI + " dialect is experimental!"); + cleaner = new OracleDBCleaner(wsJDBCConfig.getContainerName(), conn, is, wsJDBCConfig.isMultiDb()); } + else if (dbDialect == DBConstants.DB_DIALECT_ORACLE) + { + cleaner = new OracleDBCleaner(wsJDBCConfig.getContainerName(), conn, is, wsJDBCConfig.isMultiDb()); + } + else if (dbDialect == DBConstants.DB_DIALECT_PGSQL) + { + cleaner = new PgSQLDBCleaner(wsJDBCConfig.getContainerName(), conn, is, wsJDBCConfig.isMultiDb()); + } + else if (dbDialect == DBConstants.DB_DIALECT_INGRES) + { + cleaner = new IngresSQLDBCleaner(wsJDBCConfig.getContainerName(), conn, is, wsJDBCConfig.isMultiDb()); + } else { - cleaner = new SingleDBCleaner(conn, is, wsJDBCConfig.getContainerName()); + //use default DBCleaner + cleaner = new DBCleaner(wsJDBCConfig.getContainerName(), conn, is, wsJDBCConfig.isMultiDb()); } + // clean workspace try { cleaner.cleanWorkspace(); @@ -100,6 +128,15 @@ } } + /** + * Cleanup repository data from database. + * + * @param repoConfig - repository configuration + * @throws RepositoryConfigurationException - exception on parsing workspace configuration + * @throws NamingException - exception on parsing workspace configuration or getting DataSource + * @throws RepositoryException - exception on parsing workspace configuration or data cleanup + * @throws IOException - exception on parsing workspace configuration + */ public static void removeRepositoryData(RepositoryEntry repoConfig) throws RepositoryConfigurationException, NamingException, RepositoryException, IOException { @@ -109,6 +146,12 @@ } } + /** + * Make a path to scripts file according to used database dialect. + * @param dbDialect - database dialect + * @param multiDb - is multi db + * @return Path to script file + */ private static String getScriptPath(String dbDialect, boolean multiDb) { String sqlPath = "/conf/storage/cleanup/jcr-" + (multiDb ? "m" : "s"); @@ -168,5 +211,4 @@ } return sqlPath; } - } Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/IngresSQLDBCleaner.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/IngresSQLDBCleaner.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/IngresSQLDBCleaner.java 2010-09-17 09:45:55 UTC (rev 3131) @@ -0,0 +1,51 @@ +/* + * 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.SQLException; + +/** + * Created by The eXo Platform SAS. + * + * <br/>Date: + * + * @author <a href=&quot;karpenko.sergiy(a)gmail.com&quot;&gt;Karpenko Sergiy</a> + * @version $Id: IngresSQLDBCleaner.java 111 2008-11-11 11:11:11Z serg $ + */ +public class IngresSQLDBCleaner extends DBCleaner +{ + /** + * Constructor. + */ + public IngresSQLDBCleaner(String containerName, Connection connection, InputStream inputStream, boolean isMultiDB) + throws IOException + { + super(containerName, connection, inputStream, isMultiDB); + } + + /** + * {@inheritDoc} + */ + @Override + protected boolean isTableExists(Connection conn, String tableName) throws SQLException + { + return super.isTableExists(conn, tableName.toUpperCase().toLowerCase()); + } +} Deleted: 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 2010-09-17 08:39:43 UTC (rev 3130) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/MultiDBCleaner.java 2010-09-17 09:45:55 UTC (rev 3131) @@ -1,37 +0,0 @@ -/* - * 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/OracleDBCleaner.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/OracleDBCleaner.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/OracleDBCleaner.java 2010-09-17 09:45:55 UTC (rev 3131) @@ -0,0 +1,80 @@ +/* + * 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.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: OracleDBCleaner.java 111 2008-11-11 11:11:11Z serg $ + */ +public class OracleDBCleaner extends DBCleaner +{ + + /** + * Constructor. + */ + public OracleDBCleaner(String containerName, Connection connection, InputStream inputStream, boolean isMultiDB) + throws IOException + { + super(containerName, connection, inputStream, isMultiDB); + } + + /** + * {@inheritDoc} + */ + @Override + protected boolean isTableExists(Connection conn, String tableName) throws SQLException + { + Statement st = null; + try + { + st = conn.createStatement(); + st.executeUpdate("SELECT 1 FROM " + tableName); + return true; + } + catch (SQLException e) + { + // check: ORA-00942: table or view does not exist + if (e.getMessage().indexOf("ORA-00942") >= 0) + return false; + throw e; + } + finally + { + if (st != null) + { + try + { + st.close(); + } + catch (SQLException e) + { + LOG.error("Can't close the Statement: " + e); + } + } + } + } +} Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/PgSQLDBCleaner.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/PgSQLDBCleaner.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/PgSQLDBCleaner.java 2010-09-17 09:45:55 UTC (rev 3131) @@ -0,0 +1,51 @@ +/* + * 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.SQLException; + +/** + * Created by The eXo Platform SAS. + * + * <br/>Date: + * + * @author <a href=&quot;karpenko.sergiy(a)gmail.com&quot;&gt;Karpenko Sergiy</a> + * @version $Id: PgSQLDBCLeaner.java 111 2008-11-11 11:11:11Z serg $ + */ +public class PgSQLDBCleaner extends DBCleaner +{ + /** + * Constructor. + */ + public PgSQLDBCleaner(String containerName, Connection connection, InputStream inputStream, boolean isMultiDB) + throws IOException + { + super(containerName, connection, inputStream, isMultiDB); + } + + /** + * {@inheritDoc} + */ + @Override + protected boolean isTableExists(Connection conn, String tableName) throws SQLException + { + return super.isTableExists(conn, tableName.toUpperCase().toLowerCase()); + } +} Deleted: 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 2010-09-17 08:39:43 UTC (rev 3130) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/SingleDBCleaner.java 2010-09-17 09:45:55 UTC (rev 3131) @@ -1,163 +0,0 @@ -/* - * 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 org.exoplatform.services.jcr.impl.util.SecurityHelper; - -import java.io.IOException; -import java.io.InputStream; -import java.security.PrivilegedExceptionAction; -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; - final 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 - { - final PreparedStatement getChildIds = getChildItems; - ResultSet result = - (ResultSet)SecurityHelper.doPriviledgedSQLExceptionAction(new PrivilegedExceptionAction<Object>() - { - public Object run() throws Exception - { - return getChildIds.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; - final String q = REMOVE_ITEMS.replace("?", childListBuilder.toString()); - SecurityHelper.doPriviledgedSQLExceptionAction(new PrivilegedExceptionAction<Object>() - { - public Object run() throws Exception - { - removeItems.executeUpdate(q); - return null; - } - }); - } - while (true); - } - finally - { - if (getChildItems != null) - { - getChildItems.close(); - getChildItems = null; - } - if (removeItems != null) - { - removeItems.close(); - } - } - } -}