Author: nfilotto Date: 2010-03-19 03:22:38 -0400 (Fri, 19 Mar 2010) New Revision: 2084 Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DefaultOracleConnectionFactory.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCStorageConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCWorkspaceDataContainer.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBMultiDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBSingleDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleConnectionFactory.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java Log: EXOJCR-519: Perfs have been improved on HSQLDB, MySQL and Oracle. For HSQLDB: The filters of the complex queries were not on the right order for MySQL: The complex traverseQPath method has been re-added since we get much better performances with this method than with the old one, this method is only slower with HSQLDB so only HSQLDB uses the old one. for Oracle: Hints have been added to complex queries to prevent oracle to use the bad execution plan Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCStorageConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCStorageConnection.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCStorageConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -946,6 +946,16 @@ */ protected QPath traverseQPath(String cpid) throws SQLException, InvalidItemStateException, IllegalNameException { + return traverseQPathSQ(cpid); + } + + /** + * The method <code>traverseQPath</code> implemented thanks to simple queries. It allows + * to use Simple Queries instead of Complex Queries when complex queries are much slower such + * as with HSQLDB for example. + */ + protected QPath traverseQPathSQ(String cpid) throws SQLException, InvalidItemStateException, IllegalNameException + { // get item by Identifier usecase List<QPathEntry> qrpath = new ArrayList<QPathEntry>(); // reverted path String caid = cpid; // container ancestor id Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -28,6 +28,7 @@ import org.exoplatform.services.jcr.datamodel.NodeData; import org.exoplatform.services.jcr.datamodel.PropertyData; import org.exoplatform.services.jcr.datamodel.QPath; +import org.exoplatform.services.jcr.datamodel.QPathEntry; import org.exoplatform.services.jcr.datamodel.ValueData; import org.exoplatform.services.jcr.impl.Constants; import org.exoplatform.services.jcr.impl.dataflow.persistent.ByteArrayPersistedValueData; @@ -54,6 +55,7 @@ import java.util.StringTokenizer; import java.util.TreeSet; +import javax.jcr.InvalidItemStateException; import javax.jcr.RepositoryException; /** @@ -91,6 +93,11 @@ protected String FIND_REFERENCE_PROPERTIES_CQ; /** + * FIND_ITEM_QPATH_BY_ID_CQ. + */ + protected String FIND_ITEM_QPATH_BY_ID_CQ; + + /** * The comparator used to sort the value data */ private static Comparator<ValueData> COMPARATOR_VALUE_DATA = new Comparator<ValueData>() @@ -724,6 +731,84 @@ } } + /** + * {@inheritDoc} + */ + @Override + protected QPath traverseQPath(String cpid) throws SQLException, InvalidItemStateException, IllegalNameException + { + String id = getIdentifier(cpid); + if (id.equals(Constants.ROOT_UUID)) + { + return Constants.ROOT_PATH; + } + // get item by Identifier usecase + List<QPathEntry> qrpath = new ArrayList<QPathEntry>(); // reverted path + String caid = cpid; // container ancestor id + boolean isRoot = false; + do + { + ResultSet result = null; + try + { + result = findItemQPathByIdentifierCQ(caid); + if (!result.next()) + throw new InvalidItemStateException("Parent not found, uuid: " + getIdentifier(caid)); + + QPathEntry qpe1 = + new QPathEntry(InternalQName.parse(result.getString(COLUMN_NAME)), result.getInt(COLUMN_INDEX)); + boolean isChild = caid.equals(result.getString(COLUMN_ID)); + caid = result.getString(COLUMN_PARENTID); + if (result.next()) + { + QPathEntry qpe2 = + new QPathEntry(InternalQName.parse(result.getString(COLUMN_NAME)), result.getInt(COLUMN_INDEX)); + if (isChild) + { + // The child is the first result then we have the parent + qrpath.add(qpe1); + qrpath.add(qpe2); + // We need to take the value of the parent node + caid = result.getString(COLUMN_PARENTID); + } + else + { + // The parent is the first result then we have the child + qrpath.add(qpe2); + qrpath.add(qpe1); + } + } + else + { + qrpath.add(qpe1); + } + } + finally + { + result.close(); + } + if (caid.equals(Constants.ROOT_PARENT_UUID) || (id = getIdentifier(caid)).equals(Constants.ROOT_UUID)) + { + if (id.equals(Constants.ROOT_UUID)) + { + qrpath.add(Constants.ROOT_PATH.getEntries()[0]); + } + isRoot = true; + } + } + while (!isRoot); + + QPathEntry[] qentries = new QPathEntry[qrpath.size()]; + int qi = 0; + for (int i = qrpath.size() - 1; i >= 0; i--) + { + qentries[qi++] = qrpath.get(i); + } + return new QPath(qentries); + } + + protected abstract ResultSet findItemQPathByIdentifierCQ(String identifier) throws SQLException; + protected abstract ResultSet findChildNodesByParentIdentifierCQ(String parentIdentifier) throws SQLException; protected abstract ResultSet findChildPropertiesByParentIdentifierCQ(String parentIdentifier) throws SQLException; Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCWorkspaceDataContainer.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCWorkspaceDataContainer.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCWorkspaceDataContainer.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -24,13 +24,14 @@ import org.exoplatform.services.jcr.impl.storage.jdbc.DBConstants; import org.exoplatform.services.jcr.impl.storage.jdbc.JDBCWorkspaceDataContainer; import org.exoplatform.services.jcr.impl.storage.jdbc.db.GenericConnectionFactory; -import org.exoplatform.services.jcr.impl.storage.jdbc.init.StorageDBInitializer; import org.exoplatform.services.jcr.impl.storage.jdbc.init.IngresSQLDBInitializer; import org.exoplatform.services.jcr.impl.storage.jdbc.init.OracleDBInitializer; import org.exoplatform.services.jcr.impl.storage.jdbc.init.PgSQLDBInitializer; +import org.exoplatform.services.jcr.impl.storage.jdbc.init.StorageDBInitializer; import org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db.GenericCQConnectionFactory; import org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db.HSQLDBConnectionFactory; import org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db.MySQLConnectionFactory; +import org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db.DefaultOracleConnectionFactory; import org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db.OracleConnectionFactory; import org.exoplatform.services.jcr.impl.util.jdbc.DBInitializerException; import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider; @@ -92,7 +93,15 @@ LOG.warn(DBConstants.DB_DIALECT_ORACLEOCI + " dialect is experimental!"); // sample of connection factory customization if (dbSourceName != null) - this.connFactory = defaultConnectionFactory(); + { + DataSource ds = (DataSource)new InitialContext().lookup(dbSourceName); + if (ds != null) + this.connFactory = + new DefaultOracleConnectionFactory(ds, containerName, multiDb, valueStorageProvider, maxBufferSize, + swapDirectory, swapCleaner); + else + throw new RepositoryException("Datasource '" + dbSourceName + "' is not bound in this context."); + } else this.connFactory = new OracleConnectionFactory(dbDriver, dbUrl, dbUserName, dbPassword, containerName, multiDb, @@ -106,7 +115,20 @@ else if (dbDialect == DBConstants.DB_DIALECT_ORACLE) { - this.connFactory = defaultConnectionFactory(); + if (dbSourceName != null) + { + DataSource ds = (DataSource)new InitialContext().lookup(dbSourceName); + if (ds != null) + this.connFactory = + new DefaultOracleConnectionFactory(ds, containerName, multiDb, valueStorageProvider, maxBufferSize, + swapDirectory, swapCleaner); + else + throw new RepositoryException("Datasource '" + dbSourceName + "' is not bound in this context."); + } + else + this.connFactory = + new DefaultOracleConnectionFactory(dbDriver, dbUrl, dbUserName, dbPassword, containerName, multiDb, + valueStorageProvider, maxBufferSize, swapDirectory, swapCleaner); sqlPath = "/conf/storage/jcr-" + (multiDb ? "m" : "s") + "jdbc.ora.sql"; dbInitilizer = new OracleDBInitializer(containerName, this.connFactory.getJdbcConnection(), sqlPath, multiDb); Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DefaultOracleConnectionFactory.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DefaultOracleConnectionFactory.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/DefaultOracleConnectionFactory.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -0,0 +1,123 @@ +/* + * 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&lt;http://www.gnu.org/licenses/&gt;. + */ +package org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db; + +import org.exoplatform.services.jcr.impl.util.io.FileCleaner; +import org.exoplatform.services.jcr.storage.WorkspaceStorageConnection; +import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider; + +import java.io.File; +import java.sql.SQLException; + +import javax.jcr.RepositoryException; +import javax.sql.DataSource; + +/** + * Created by The eXo Platform SAS + * Author : Nicolas Filotto + * nicolas.filotto(a)exoplatform.com + * 19 mars 2010 + */ +public class DefaultOracleConnectionFactory extends GenericCQConnectionFactory +{ + /** + * DefaultOracleConnectionFactory constructor. + * + * @param dataSource + * - DataSource + * @param containerName + * - Container name (see configuration) + * @param multiDb + * - multidatabase state flag + * @param valueStorageProvider + * - external Value Storages provider + * @param maxBufferSize + * - Maximum buffer size (see configuration) + * @param swapDirectory + * - Swap directory (see configuration) + * @param swapCleaner + * - Swap cleaner (internal FileCleaner). + */ + public DefaultOracleConnectionFactory(DataSource dataSource, String containerName, boolean multiDb, + ValueStoragePluginProvider valueStorageProvider, int maxBufferSize, File swapDirectory, FileCleaner swapCleaner) + { + + super(dataSource, containerName, multiDb, valueStorageProvider, maxBufferSize, swapDirectory, swapCleaner); + } + + /** + * DefaultOracleConnectionFactory constructor. + * + *@param dataSource + * - DataSource + * @param dbDriver + * - JDBC Driver + * @param dbUrl + * - JDBC URL + * @param dbUserName + * - database username + * @param dbPassword + * - database user password + * @param containerName + * - Container name (see configuration) + * @param multiDb + * - multidatabase state flag + * @param valueStorageProvider + * - external Value Storages provider + * @param maxBufferSize + * - Maximum buffer size (see configuration) + * @param swapDirectory + * - Swap directory (see configuration) + * @param swapCleaner + * - Swap cleaner (internal FileCleaner). + * @throws RepositoryException + * if error eccurs + */ + public DefaultOracleConnectionFactory(String dbDriver, String dbUrl, String dbUserName, String dbPassword, + String containerName, boolean multiDb, ValueStoragePluginProvider valueStorageProvider, int maxBufferSize, + File swapDirectory, FileCleaner swapCleaner) throws RepositoryException + { + + super(dbDriver, dbUrl, dbUserName, dbPassword, containerName, multiDb, valueStorageProvider, maxBufferSize, + swapDirectory, swapCleaner); + } + + /** + * {@inheritDoc} + */ + @Override + public WorkspaceStorageConnection openConnection(boolean readOnly) throws RepositoryException + { + try + { + + if (multiDb) + { + return new OracleMultiDbJDBCConnection(getJdbcConnection(readOnly), readOnly, containerName, + valueStorageProvider, maxBufferSize, swapDirectory, swapCleaner); + } + + return new OracleSingleDbJDBCConnection(getJdbcConnection(readOnly), readOnly, containerName, + valueStorageProvider, maxBufferSize, swapDirectory, swapCleaner); + + } + catch (SQLException e) + { + throw new RepositoryException(e); + } + } +} Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBMultiDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBMultiDbJDBCConnection.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBMultiDbJDBCConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -16,6 +16,8 @@ */ package org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db; +import org.exoplatform.services.jcr.datamodel.IllegalNameException; +import org.exoplatform.services.jcr.datamodel.QPath; import org.exoplatform.services.jcr.impl.util.io.FileCleaner; import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider; @@ -23,6 +25,8 @@ import java.sql.Connection; import java.sql.SQLException; +import javax.jcr.InvalidItemStateException; + /** * Created by The eXo Platform SAS * @@ -76,5 +80,23 @@ FIND_NODES_BY_PARENTID = "select * from JCR_MITEM" + " where PARENT_ID=? and I_CLASS=1" + " order by N_ORDER_NUM"; FIND_NODES_COUNT_BY_PARENTID = "select count(ID) from JCR_MITEM" + " where PARENT_ID=? and I_CLASS=1"; FIND_PROPERTIES_BY_PARENTID = "select * from JCR_MITEM" + " where PARENT_ID=? and I_CLASS=2" + " order by ID"; + FIND_NODES_BY_PARENTID_CQ = + "select I.*, P.NAME AS PROP_NAME, V.ORDER_NUM, V.DATA" + + " from JCR_MITEM I, JCR_MITEM P, JCR_MVALUE V" + + " where I.PARENT_ID=? and I.I_CLASS=1 and (P.PARENT_ID=I.ID and P.I_CLASS=2 and (P.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or P.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions') and V.PROPERTY_ID=P.ID)" + + " order by I.N_ORDER_NUM, I.ID"; + FIND_PROPERTIES_BY_PARENTID_CQ = + "select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED," + + " V.ORDER_NUM, V.DATA, V.STORAGE_DESC from JCR_MITEM I LEFT OUTER JOIN JCR_MVALUE V ON (V.PROPERTY_ID=I.ID)" + + " where I.PARENT_ID=? and I.I_CLASS=2 order by I.NAME"; } + + /** + * Use simple queries since it is much faster + */ + @Override + protected QPath traverseQPath(String cpid) throws SQLException, InvalidItemStateException, IllegalNameException + { + return traverseQPathSQ(cpid); + } } Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBSingleDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBSingleDbJDBCConnection.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBSingleDbJDBCConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -16,6 +16,8 @@ */ package org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db; +import org.exoplatform.services.jcr.datamodel.IllegalNameException; +import org.exoplatform.services.jcr.datamodel.QPath; import org.exoplatform.services.jcr.impl.util.io.FileCleaner; import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider; @@ -24,6 +26,8 @@ import java.sql.ResultSet; import java.sql.SQLException; +import javax.jcr.InvalidItemStateException; + /** * Created by The eXo Platform SAS * @@ -85,9 +89,32 @@ "select count(ID) from JCR_SITEM" + " where PARENT_ID=? and I_CLASS=1 and CONTAINER_NAME=?"; FIND_PROPERTIES_BY_PARENTID = "select * from JCR_SITEM" + " where PARENT_ID=? and I_CLASS=2 and CONTAINER_NAME=?" + " order by ID"; + FIND_NODES_BY_PARENTID_CQ = + "select I.*, P.NAME AS PROP_NAME, V.ORDER_NUM, V.DATA" + + " from JCR_SITEM I, JCR_SITEM P, JCR_SVALUE V" + + " where I.PARENT_ID=? and I.I_CLASS=1 and I.CONTAINER_NAME=? and" + + " P.PARENT_ID=I.ID and P.I_CLASS=2 and P.CONTAINER_NAME=? and (P.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or P.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions')" + + " and V.PROPERTY_ID=P.ID order by I.N_ORDER_NUM, I.ID"; + FIND_PROPERTIES_BY_PARENTID_CQ = + "select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED, V.ORDER_NUM," + + " V.DATA, V.STORAGE_DESC from JCR_SITEM I LEFT OUTER JOIN JCR_SVALUE V ON (V.PROPERTY_ID=I.ID)" + + " where I.PARENT_ID=? and I.I_CLASS=2 and I.CONTAINER_NAME=? order by I.NAME"; + FIND_REFERENCE_PROPERTIES_CQ = + "select P.ID, P.PARENT_ID, P.VERSION, P.P_TYPE, P.P_MULTIVALUED, P.NAME, V.ORDER_NUM, V.DATA, V.STORAGE_DESC" + + " from JCR_SREF R, JCR_SITEM P, JCR_SVALUE V" + + " where R.NODE_ID=? and P.ID=R.PROPERTY_ID and P.I_CLASS=2 and P.CONTAINER_NAME=? and V.PROPERTY_ID=P.ID order by R.PROPERTY_ID"; } /** + * Use simple queries since it is much faster + */ + @Override + protected QPath traverseQPath(String cpid) throws SQLException, InvalidItemStateException, IllegalNameException + { + return traverseQPathSQ(cpid); + } + + /** * {@inheritDoc} */ @Override @@ -169,4 +196,39 @@ findPropertiesByParentId.setString(2, containerName); return findPropertiesByParentId.executeQuery(); } + + + /** + * {@inheritDoc} + */ + @Override + protected ResultSet findChildNodesByParentIdentifierCQ(String parentIdentifier) throws SQLException + { + if (findNodesByParentIdCQ == null) + findNodesByParentIdCQ = dbConnection.prepareStatement(FIND_NODES_BY_PARENTID_CQ); + else + findNodesByParentIdCQ.clearParameters(); + + findNodesByParentIdCQ.setString(1, parentIdentifier); + findNodesByParentIdCQ.setString(2, containerName); + findNodesByParentIdCQ.setString(3, containerName); + return findNodesByParentIdCQ.executeQuery(); + } + + /** + * {@inheritDoc} + */ + @Override + protected ResultSet findChildPropertiesByParentIdentifierCQ(String parentIdentifier) throws SQLException + { + if (findPropertiesByParentIdCQ == null) + findPropertiesByParentIdCQ = dbConnection.prepareStatement(FIND_PROPERTIES_BY_PARENTID_CQ); + else + findPropertiesByParentIdCQ.clearParameters(); + + findPropertiesByParentIdCQ.setString(1, parentIdentifier); + findPropertiesByParentIdCQ.setString(2, containerName); + return findPropertiesByParentIdCQ.executeQuery(); + + } } Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -215,6 +215,11 @@ + " from JCR_MITEM I, JCR_MVALUE V" + " where I.I_CLASS=2 and I.PARENT_ID=? and (I.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or I.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions') and I.ID=V.PROPERTY_ID"; + FIND_ITEM_QPATH_BY_ID_CQ = + "select I.ID, I.PARENT_ID, I.NAME, I.I_INDEX" + + " from JCR_MITEM I, (SELECT ID, PARENT_ID from JCR_MITEM where ID=?) J" + + " where I.ID = J.ID or I.ID = J.PARENT_ID"; + FIND_NODES_COUNT_BY_PARENTID = "select count(ID) from JCR_MITEM" + " where I_CLASS=1 and PARENT_ID=?"; FIND_PROPERTIES_BY_PARENTID = "select * from JCR_MITEM" + " where I_CLASS=2 and PARENT_ID=?" + " order by NAME"; @@ -652,6 +657,21 @@ * {@inheritDoc} */ @Override + protected ResultSet findItemQPathByIdentifierCQ(String identifier) throws SQLException + { + if (findItemQPathByIdentifierCQ == null) + findItemQPathByIdentifierCQ = dbConnection.prepareStatement(FIND_ITEM_QPATH_BY_ID_CQ); + else + findItemQPathByIdentifierCQ.clearParameters(); + + findItemQPathByIdentifierCQ.setString(1, identifier); + return findItemQPathByIdentifierCQ.executeQuery(); + } + + /** + * {@inheritDoc} + */ + @Override protected ResultSet findReferencePropertiesCQ(String nodeIdentifier) throws SQLException { if (findReferencePropertiesCQ == null) Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleConnectionFactory.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleConnectionFactory.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleConnectionFactory.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -41,7 +41,7 @@ * @author <a href="mailto:peter.nedonosko@exoplatform.com.ua">Peter Nedonosko</a> * @version $Id$ */ -public class OracleConnectionFactory extends GenericCQConnectionFactory +public class OracleConnectionFactory extends DefaultOracleConnectionFactory { public static int CONNCACHE_MAX_LIMIT = 25; Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -0,0 +1,83 @@ +/* + * 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&lt;http://www.gnu.org/licenses/&gt;. + */ +package org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db; + +import org.exoplatform.services.jcr.impl.util.io.FileCleaner; +import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider; + +import java.io.File; +import java.sql.Connection; +import java.sql.SQLException; + +/** + * Created by The eXo Platform SAS + * Author : Nicolas Filotto + * nicolas.filotto(a)exoplatform.com + * 19 mars 2010 + */ +public class OracleMultiDbJDBCConnection extends MultiDbJDBCConnection +{ + + /** + * Oracle Multidatabase JDBC Connection constructor. + * + * @param dbConnection + * JDBC connection, shoudl be opened before + * @param readOnly + * boolean if true the dbConnection was marked as READ-ONLY. + * @param containerName + * Workspace Storage Container name (see configuration) + * @param valueStorageProvider + * External Value Storages provider + * @param maxBufferSize + * Maximum buffer size (see configuration) + * @param swapDirectory + * Swap directory File (see configuration) + * @param swapCleaner + * Swap cleaner (internal FileCleaner). + * @throws SQLException + * + * @see org.exoplatform.services.jcr.impl.util.io.FileCleaner + */ + public OracleMultiDbJDBCConnection(Connection dbConnection, boolean readOnly, String containerName, + ValueStoragePluginProvider valueStorageProvider, int maxBufferSize, File swapDirectory, FileCleaner swapCleaner) + throws SQLException + { + + super(dbConnection, readOnly, containerName, valueStorageProvider, maxBufferSize, swapDirectory, swapCleaner); + } + + /** + * {@inheritDoc} + */ + @Override + protected void prepareQueries() throws SQLException + { + + super.prepareQueries(); + FIND_NODES_BY_PARENTID_CQ = + "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(P JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/ I.*, P.NAME AS PROP_NAME, V.ORDER_NUM, V.DATA" + + " from JCR_MITEM I, JCR_MITEM P, JCR_MVALUE V" + + " where I.I_CLASS=1 and I.PARENT_ID=? and" + + " P.I_CLASS=2 and P.PARENT_ID=I.ID and (P.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or P.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions')" + + " and V.PROPERTY_ID=P.ID order by I.N_ORDER_NUM, I.ID"; + FIND_PROPERTIES_BY_PARENTID_CQ = + "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/ I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED," + + " V.ORDER_NUM, V.DATA, V.STORAGE_DESC from JCR_MITEM I LEFT OUTER JOIN JCR_MVALUE V ON (V.PROPERTY_ID=I.ID)" + + " where I.I_CLASS=2 and I.PARENT_ID=? order by I.NAME"; + } +} Added: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -0,0 +1,83 @@ +/* + * 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&lt;http://www.gnu.org/licenses/&gt;. + */ +package org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.db; + +import org.exoplatform.services.jcr.impl.util.io.FileCleaner; +import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider; + +import java.io.File; +import java.sql.Connection; +import java.sql.SQLException; + +/** + * Created by The eXo Platform SAS + * Author : Nicolas Filotto + * nicolas.filotto(a)exoplatform.com + * 19 mars 2010 + */ +public class OracleSingleDbJDBCConnection extends SingleDbJDBCConnection +{ + + /** + * Oracle Singledatabase JDBC Connection constructor. + * + * @param dbConnection + * JDBC connection, should be opened before + * @param readOnly + * boolean if true the dbConnection was marked as READ-ONLY. + * @param containerName + * Workspace Storage Container name (see configuration) + * @param valueStorageProvider + * External Value Storages provider + * @param maxBufferSize + * Maximum buffer size (see configuration) + * @param swapDirectory + * Swap directory File (see configuration) + * @param swapCleaner + * Swap cleaner (internal FileCleaner). + * @throws SQLException + * + * @see org.exoplatform.services.jcr.impl.util.io.FileCleaner + */ + public OracleSingleDbJDBCConnection(Connection dbConnection, boolean readOnly, String containerName, + ValueStoragePluginProvider valueStorageProvider, int maxBufferSize, File swapDirectory, FileCleaner swapCleaner) + throws SQLException + { + + super(dbConnection, readOnly, containerName, valueStorageProvider, maxBufferSize, swapDirectory, swapCleaner); + } + + /** + * {@inheritDoc} + */ + @Override + protected void prepareQueries() throws SQLException + { + + super.prepareQueries(); + FIND_NODES_BY_PARENTID_CQ = + "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(P JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/ I.*, P.NAME AS PROP_NAME, V.ORDER_NUM, V.DATA" + + " from JCR_SITEM I, JCR_SITEM P, JCR_SVALUE V" + + " where I.I_CLASS=1 and I.CONTAINER_NAME=? and I.PARENT_ID=? and" + + " P.I_CLASS=2 and P.CONTAINER_NAME=? and P.PARENT_ID=I.ID and (P.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or P.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or P.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions')" + + " and V.PROPERTY_ID=P.ID order by I.N_ORDER_NUM, I.ID"; + FIND_PROPERTIES_BY_PARENTID_CQ = + "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/ I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_CLASS, I.I_INDEX, I.N_ORDER_NUM, I.P_TYPE, I.P_MULTIVALUED, V.ORDER_NUM," + + " V.DATA, V.STORAGE_DESC from JCR_SITEM I LEFT OUTER JOIN JCR_SVALUE V ON (V.PROPERTY_ID=I.ID)" + + " where I.I_CLASS=2 and I.CONTAINER_NAME=? and I.PARENT_ID=? order by I.NAME"; + } +} Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2010-03-18 14:17:20 UTC (rev 2083) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2010-03-19 07:22:38 UTC (rev 2084) @@ -223,6 +223,11 @@ + " from JCR_SITEM I, JCR_SVALUE V" + " where I.I_CLASS=2 and I.CONTAINER_NAME=? and I.PARENT_ID=? and (I.NAME='[http://www.jcp.org/jcr/1.0]primaryType' or I.NAME='[http://www.jcp.org/jcr/1.0]mixinTypes' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]owner' or I.NAME='[http://www.exoplatform.com/jcr/exo/1.0]permissions') and I.ID=V.PROPERTY_ID"; + FIND_ITEM_QPATH_BY_ID_CQ = + "select I.ID, I.PARENT_ID, I.NAME, I.I_INDEX" + + " from JCR_SITEM I, (SELECT ID, PARENT_ID from JCR_SITEM where ID=?) J" + + " where I.ID = J.ID or I.ID = J.PARENT_ID"; + FIND_NODES_COUNT_BY_PARENTID = "select count(ID) from JCR_SITEM" + " where I_CLASS=1 and CONTAINER_NAME=? and PARENT_ID=?"; @@ -676,6 +681,21 @@ * {@inheritDoc} */ @Override + protected ResultSet findItemQPathByIdentifierCQ(String identifier) throws SQLException + { + if (findItemQPathByIdentifierCQ == null) + findItemQPathByIdentifierCQ = dbConnection.prepareStatement(FIND_ITEM_QPATH_BY_ID_CQ); + else + findItemQPathByIdentifierCQ.clearParameters(); + + findItemQPathByIdentifierCQ.setString(1, identifier); + return findItemQPathByIdentifierCQ.executeQuery(); + } + + /** + * {@inheritDoc} + */ + @Override protected ResultSet findReferencePropertiesCQ(String nodeIdentifier) throws SQLException { if (findReferencePropertiesCQ == null)