Author: nfilotto Date: 2009-12-31 04:05:39 -0500 (Thu, 31 Dec 2009) New Revision: 1262 Modified: jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java Log: EXOJCR-302: Rework of JDBC level to use complex queries We use a new query for findChildNodesByParentIdentifierCQ that is better than the previous one for the following reasons: 1. We don't have to keep all the data into a temporary map since everything cans be done on the fly 2. We get much better performances with a big amount of sub nodes (the most important reason) 3. We don't use an outer join which means that the request should be easily supported by all the main databases Modified: jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java =================================================================== --- jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java 2009-12-30 15:11:04 UTC (rev 1261) +++ jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/CQJDBCStorageConnection.java 2009-12-31 09:05:39 UTC (rev 1262) @@ -33,6 +33,7 @@ import org.exoplatform.services.jcr.datamodel.ValueData; import org.exoplatform.services.jcr.impl.Constants; 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; import org.exoplatform.services.jcr.storage.value.ValueStoragePluginProvider; @@ -145,25 +146,36 @@ { // query will return nodes and properties in same result set ResultSet resultSet = findChildNodesByParentIdentifierCQ(getInternalId(parent.getIdentifier())); - if (resultSet.next()) + TempNodeData data = null; + List<NodeData> childNodes = new ArrayList<NodeData>(); + while (resultSet.next()) { - Map<String, TempNodeData> tempNodes = loadTempNodesFromResultSet(resultSet); - - List<NodeData> childNodes = new ArrayList<NodeData>(tempNodes.size()); - QPath parentQPath = parent.getQPath(); - AccessControlList parentACL = parent.getACL(); - for (TempNodeData data : tempNodes.values()) + if (data == null) { - NodeData nodeData = loadNodeFromTemporaryNodeData(data, parentQPath, parentACL); + data = new TempNodeData(resultSet); + } + else if (!resultSet.getString(COLUMN_ID).equals(data.cid)) + { + NodeData nodeData = loadNodeFromTemporaryNodeData(data, parent.getQPath(), parent.getACL()); childNodes.add(nodeData); + data = new TempNodeData(resultSet); } - return childNodes; + Map<String, List<byte[]>> properties = data.properties; + String key = resultSet.getString("PROP_NAME"); + List<byte[]> values = properties.get(key); + if (values == null) + { + values = new ArrayList<byte[]>(); + properties.put(key, values); + } + values.add(resultSet.getBytes(COLUMN_VDATA)); } - else + if (data != null) { - return new ArrayList<NodeData>(); + NodeData nodeData = loadNodeFromTemporaryNodeData(data, parent.getQPath(), parent.getACL()); + childNodes.add(nodeData); } - + return childNodes; } catch (SQLException e) { @@ -508,9 +520,10 @@ // PRIMARY List<byte[]> primaryType = properties.get(Constants.JCR_PRIMARYTYPE.getAsString()); - if (primaryType == null || primaryType.size() == 0) + if (primaryType == null || primaryType.isEmpty()) { - throw new SQLException("Node finded but primaryType property not " + cid); + throw new PrimaryTypeNotFoundException("FATAL ERROR primary type record not found. Node " + + qpath.getAsString() + ", id " + cid + ", container " + this.containerName, null); } byte[] data = primaryType.get(0); Modified: jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java =================================================================== --- jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2009-12-30 15:11:04 UTC (rev 1261) +++ jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2009-12-31 09:05:39 UTC (rev 1262) @@ -212,11 +212,10 @@ FIND_NODES_BY_PARENTID = "select * from JCR_MITEM" + " where I_CLASS=1 and PARENT_ID=?" + " order by N_ORDER_NUM"; FIND_NODES_BY_PARENTID_CQ = - "select I.*, V.DATA, V.ORDER_NUM" - + " from JCR_MITEM I LEFT OUTER JOIN JCR_MVALUE V ON (V.PROPERTY_ID=I.ID), (select ID from JCR_MITEM where PARENT_ID=? and I_CLASS=1) I2" - + " where (PARENT_ID=? and I.I_CLASS=1 and I.ID=I2.ID) or (I.I_CLASS=2 and I.PARENT_ID=I2.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')) order by I.I_CLASS, I.N_ORDER_NUM, V.ORDER_NUM"; + "select I.*, P.NAME AS PROP_NAME, V.ORDER_NUM, V.DATA" + + " from (select * from JCR_SITEM where PARENT_ID=? AND I_CLASS=1) I, JCR_SITEM P, JCR_SVALUE V" + + " where (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, PROP_NAME DESC, V.ORDER_NUM"; FIND_NODES_COUNT_BY_PARENTID = "select count(ID) from JCR_MITEM" + " where I_CLASS=1 and PARENT_ID=?"; @@ -491,7 +490,6 @@ findNodesByParentIdCQ.clearParameters(); findNodesByParentIdCQ.setString(1, parentIdentifier); - findNodesByParentIdCQ.setString(2, parentIdentifier); return findNodesByParentIdCQ.executeQuery(); } Modified: jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java =================================================================== --- jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2009-12-30 15:11:04 UTC (rev 1261) +++ jcr/branches/1.12.0-OPT/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2009-12-31 09:05:39 UTC (rev 1262) @@ -217,11 +217,10 @@ "select * from JCR_SITEM" + " where I_CLASS=1 and CONTAINER_NAME=? and PARENT_ID=?" + " order by N_ORDER_NUM"; FIND_NODES_BY_PARENTID_CQ = - "select I.*, V.DATA, V.ORDER_NUM" - + " from JCR_SITEM I LEFT OUTER JOIN JCR_SVALUE V ON (V.PROPERTY_ID=I.ID), (select ID from JCR_SITEM where I_CLASS=1 and CONTAINER_NAME=? and PARENT_ID=?) I2" - + " where (I.PARENT_ID=? and I.I_CLASS=1 and I.ID=I2.ID) or (I.I_CLASS=2 and I.PARENT_ID=I2.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')) order by I.I_CLASS, I.N_ORDER_NUM"; //, V.ORDER_NUM + "select I.*, P.NAME AS PROP_NAME, V.ORDER_NUM, V.DATA" + + " from (select * from JCR_SITEM where CONTAINER_NAME=? and PARENT_ID=? AND I_CLASS=1) I, JCR_SITEM P, JCR_SVALUE V" + + " where (P.CONTAINER_NAME=? 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, PROP_NAME DESC, V.ORDER_NUM"; FIND_NODES_COUNT_BY_PARENTID = "select count(ID) from JCR_SITEM" + " where I_CLASS=1 and CONTAINER_NAME=? and PARENT_ID=?"; @@ -648,7 +647,6 @@ @Override protected ResultSet findChildNodesByParentIdentifierCQ(String parentIdentifier) throws SQLException { - if (findNodesByParentIdCQ == null) findNodesByParentIdCQ = dbConnection.prepareStatement(FIND_NODES_BY_PARENTID_CQ); else @@ -656,7 +654,7 @@ findNodesByParentIdCQ.setString(1, containerName); findNodesByParentIdCQ.setString(2, parentIdentifier); - findNodesByParentIdCQ.setString(3, parentIdentifier); + findNodesByParentIdCQ.setString(3, containerName); return findNodesByParentIdCQ.executeQuery(); }