Author: nfilotto Date: 2010-03-19 13:45:28 -0400 (Fri, 19 Mar 2010) New Revision: 2087 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/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/OracleMultiDbJDBCConnection.java jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java Log: EXOJCR-519: A. FIND_REFERENCE_PROPERTIES_CQ has been removed because: 1. To be improved on Oracle we need to add indexes which will impact the performances of the write operation 2. This method is not critical in term of business value since it is not used too often 3. This method doesn't need to be scalable in term of total amount of results since we should never have more than 10 properties 4. This method seems to cause deadlocks on PostgresSQL B. The hints were not correct for oracle in multidb mode 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-19 15:20:47 UTC (rev 2086) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java 2010-03-19 17:45:28 UTC (rev 2087) @@ -31,7 +31,6 @@ 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; import org.exoplatform.services.jcr.impl.storage.jdbc.JDBCStorageConnection; import org.exoplatform.services.jcr.impl.storage.jdbc.PrimaryTypeNotFoundException; import org.exoplatform.services.jcr.impl.util.io.FileCleaner; @@ -88,11 +87,6 @@ protected String FIND_NODE_MAIN_PROPERTIES_BY_PARENTID_CQ; /** - * FIND_REFERENCE_PROPERTIES_CQ. - */ - protected String FIND_REFERENCE_PROPERTIES_CQ; - - /** * FIND_ITEM_QPATH_BY_ID_CQ. */ protected String FIND_ITEM_QPATH_BY_ID_CQ; @@ -352,152 +346,6 @@ } /** - * {@inheritDoc} - */ - public List<PropertyData> getReferencesData(String nodeIdentifier) throws RepositoryException, IllegalStateException - { - checkIfOpened(); - ResultSet refProps = null; - try - { - refProps = findReferencePropertiesCQ(getInternalId(nodeIdentifier)); - return loadReferences(refProps); - } - catch (SQLException e) - { - throw new RepositoryException(e); - } - catch (IOException e) - { - throw new RepositoryException(e); - } - finally - { - if (refProps != null) - { - try - { - refProps.close(); - } - catch (SQLException e) - { - LOG.error(e.getMessage(), e); - } - } - } - - } - - /** - * Load Property references - * - * @param resultSet - * @return - * @throws RepositoryException - * @throws SQLException - * @throws IOException - */ - private List<PropertyData> loadReferences(ResultSet resultSet) throws RepositoryException, SQLException, IOException - { - List<PropertyData> resultProps = new ArrayList<PropertyData>(); - - // Property Id and amount of copies in result - Map<String, Integer> dublicatedProps = new HashMap<String, Integer>(); - Map<String, PersistedPropertyData> propertyBuffer = new HashMap<String, PersistedPropertyData>(); - Map<String, List<ValueData>> valuesBuffer = new HashMap<String, List<ValueData>>(); - - try - { - while (resultSet.next()) - { - String cid = resultSet.getString(COLUMN_ID); - String identifier = getIdentifier(cid); - - int cversion = resultSet.getInt(COLUMN_VERSION); - - int valueOrderNum = resultSet.getInt(COLUMN_VORDERNUM); - PersistedPropertyData prop = propertyBuffer.get(identifier); - - if (prop == null) - { - // make temporary PropertyData without values - String cname = resultSet.getString(COLUMN_NAME); - - String cpid = resultSet.getString(COLUMN_PARENTID); - int cptype = resultSet.getInt(COLUMN_PTYPE); - boolean cpmultivalued = resultSet.getBoolean(COLUMN_PMULTIVALUED); - QPath qpath = QPath.makeChildPath(traverseQPath(cpid), InternalQName.parse(cname)); - - prop = - new PersistedPropertyData(identifier, qpath, getIdentifier(cpid), cversion, cptype, cpmultivalued, - null); // null values! - propertyBuffer.put(identifier, prop); - valuesBuffer.put(identifier, new ArrayList<ValueData>()); - dublicatedProps.put(identifier, new Integer(1)); - } - - List<ValueData> values = valuesBuffer.get(identifier); - if (valueOrderNum == 0 && values.size() > 0) - { - // ignore it, this is a new copy - Integer copies = dublicatedProps.get(identifier); - copies++; - dublicatedProps.put(identifier, copies); - } - else if (values.size() == valueOrderNum) - { - // read value and put into values buffer - final String storageId = resultSet.getString(COLUMN_VSTORAGE_DESC); - ValueData vdata = - resultSet.wasNull() ? readValueData(cid, valueOrderNum, cversion, resultSet - .getBinaryStream(COLUMN_VDATA)) : readValueData(identifier, valueOrderNum, storageId); - - values.add(vdata); - valuesBuffer.put(identifier, values); - } - } - - for (String id : propertyBuffer.keySet()) - { - - PersistedPropertyData prop = propertyBuffer.get(id); - List<ValueData> values = valuesBuffer.get(id); - Collections.sort(values, COMPARATOR_VALUE_DATA); - int count = dublicatedProps.get(id).intValue(); - - for (int i = 0; i < count; i++) - { - //make a copy - List<ValueData> newValues = new ArrayList<ValueData>(); - for (ValueData vd : values) - { - newValues.add(new ByteArrayPersistedValueData(vd.getOrderNumber(), vd.getAsByteArray())); - } - - PersistedPropertyData pdata = - new PersistedPropertyData(prop.getIdentifier(), prop.getQPath(), prop.getParentIdentifier(), prop - .getPersistedVersion(), prop.getType(), prop.isMultiValued(), newValues); - resultProps.add(pdata); - } - values.clear(); - } - } - catch (IllegalNameException e) - { - throw new RepositoryException(e); - } - finally - { - // clean buffers - propertyBuffer.clear(); - valuesBuffer.clear(); - dublicatedProps.clear(); - } - - return resultProps; - } - - /** * Read ACL Permissions from properties set. * * @param cid node id (used only for error messages) @@ -814,6 +662,4 @@ protected abstract ResultSet findChildPropertiesByParentIdentifierCQ(String parentIdentifier) throws SQLException; protected abstract ResultSet findNodeMainPropertiesByParentIdentifierCQ(String parentIdentifier) throws SQLException; - - protected abstract ResultSet findReferencePropertiesCQ(String nodeIdentifier) throws SQLException; } 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-19 15:20:47 UTC (rev 2086) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/HSQLDBSingleDbJDBCConnection.java 2010-03-19 17:45:28 UTC (rev 2087) @@ -99,10 +99,6 @@ "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"; } /** 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-19 15:20:47 UTC (rev 2086) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2010-03-19 17:45:28 UTC (rev 2087) @@ -67,8 +67,6 @@ protected PreparedStatement findReferences; - protected PreparedStatement findReferencePropertiesCQ; - protected PreparedStatement findValuesByPropertyId; protected PreparedStatement findValuesDataByPropertyId; @@ -188,11 +186,6 @@ "select P.ID, P.PARENT_ID, P.VERSION, P.P_TYPE, P.P_MULTIVALUED, P.NAME" + " from JCR_MREF R, JCR_MITEM P" + " where R.NODE_ID=? and P.ID=R.PROPERTY_ID and P.I_CLASS=2"; - 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_MREF R, JCR_MITEM P, JCR_MVALUE V" - + " where R.NODE_ID=? and P.ID=R.PROPERTY_ID and P.I_CLASS=2 and V.PROPERTY_ID=P.ID order by R.PROPERTY_ID"; - FIND_VALUES_BY_PROPERTYID = "select PROPERTY_ID, ORDER_NUM, DATA, STORAGE_DESC from JCR_MVALUE where PROPERTY_ID=? order by ORDER_NUM"; @@ -668,19 +661,4 @@ return findItemQPathByIdentifierCQ.executeQuery(); } - /** - * {@inheritDoc} - */ - @Override - protected ResultSet findReferencePropertiesCQ(String nodeIdentifier) throws SQLException - { - if (findReferencePropertiesCQ == null) - findReferencePropertiesCQ = dbConnection.prepareStatement(FIND_REFERENCE_PROPERTIES_CQ); - else - findReferencePropertiesCQ.clearParameters(); - - findReferencePropertiesCQ.setString(1, nodeIdentifier); - return findReferencePropertiesCQ.executeQuery(); - } - } Modified: 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 2010-03-19 15:20:47 UTC (rev 2086) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java 2010-03-19 17:45:28 UTC (rev 2087) @@ -70,13 +70,13 @@ 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" + "select /*+ INDEX(I JCR_IDX_MITEM_PARENT_ID) INDEX(P JCR_IDX_MITEM_PARENT_ID) INDEX(V JCR_IDX_MVALUE_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," + "select /*+ INDEX(I JCR_IDX_MITEM_PARENT_ID) INDEX(V JCR_IDX_MVALUE_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"; } 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-19 15:20:47 UTC (rev 2086) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2010-03-19 17:45:28 UTC (rev 2087) @@ -65,8 +65,6 @@ protected PreparedStatement findReferences; - protected PreparedStatement findReferencePropertiesCQ; - protected PreparedStatement findValuesByPropertyId; protected PreparedStatement findValuesStorageDescriptorsByPropertyId; @@ -195,11 +193,6 @@ "select P.ID, P.PARENT_ID, P.VERSION, P.P_TYPE, P.P_MULTIVALUED, P.NAME" + " from JCR_SREF R, JCR_SITEM P" + " where R.NODE_ID=? and P.CONTAINER_NAME=? and P.ID=R.PROPERTY_ID and P.I_CLASS=2"; - 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.CONTAINER_NAME=? and P.ID=R.PROPERTY_ID and P.I_CLASS=2 and V.PROPERTY_ID=P.ID order by R.PROPERTY_ID"; - FIND_VALUES_BY_PROPERTYID = "select PROPERTY_ID, ORDER_NUM, DATA, STORAGE_DESC from JCR_SVALUE where PROPERTY_ID=? order by ORDER_NUM"; @@ -691,20 +684,4 @@ findItemQPathByIdentifierCQ.setString(1, identifier); return findItemQPathByIdentifierCQ.executeQuery(); } - - /** - * {@inheritDoc} - */ - @Override - protected ResultSet findReferencePropertiesCQ(String nodeIdentifier) throws SQLException - { - if (findReferencePropertiesCQ == null) - findReferencePropertiesCQ = dbConnection.prepareStatement(FIND_REFERENCE_PROPERTIES_CQ); - else - findReferencePropertiesCQ.clearParameters(); - - findReferencePropertiesCQ.setString(1, nodeIdentifier); - findReferencePropertiesCQ.setString(2, containerName); - return findReferencePropertiesCQ.executeQuery(); - } }