Author: nfilotto Date: 2010-03-20 03:02:33 -0400 (Sat, 20 Mar 2010) New Revision: 2088 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/OracleMultiDbJDBCConnection.java 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/SingleDbJDBCConnection.java Log: EXOJCR-519: Avoid duplicating the complex queries just to add the hints, now we use replaceFirst to add the hints 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 17:45:28 UTC (rev 2087) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MultiDbJDBCConnection.java 2010-03-20 07:02:33 UTC (rev 2088) @@ -51,6 +51,18 @@ public class MultiDbJDBCConnection extends CQJDBCStorageConnection { + protected static final String FIND_NODES_BY_PARENTID_CQ_QUERY = + "select 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"; + + protected static final String FIND_PROPERTIES_BY_PARENTID_CQ_QUERY = + "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.I_CLASS=2 and I.PARENT_ID=? order by I.NAME"; + protected PreparedStatement findItemById; protected PreparedStatement findItemByPath; @@ -196,12 +208,7 @@ 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.*, 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_NODES_BY_PARENTID_CQ = FIND_NODES_BY_PARENTID_CQ_QUERY; FIND_NODE_MAIN_PROPERTIES_BY_PARENTID_CQ = "select I.NAME, V.DATA, V.ORDER_NUM" @@ -218,10 +225,7 @@ FIND_PROPERTIES_BY_PARENTID = "select * from JCR_MITEM" + " where I_CLASS=2 and PARENT_ID=?" + " order by NAME"; // property may contain no values - 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.I_CLASS=2 and I.PARENT_ID=? order by I.NAME"; + FIND_PROPERTIES_BY_PARENTID_CQ = FIND_PROPERTIES_BY_PARENTID_CQ_QUERY; INSERT_NODE = "insert into JCR_MITEM(ID, PARENT_ID, NAME, VERSION, I_CLASS, I_INDEX, N_ORDER_NUM) VALUES(?,?,?,?," 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 17:45:28 UTC (rev 2087) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleMultiDbJDBCConnection.java 2010-03-20 07:02:33 UTC (rev 2088) @@ -32,6 +32,15 @@ public class OracleMultiDbJDBCConnection extends MultiDbJDBCConnection { + protected static final String FIND_NODES_BY_PARENTID_CQ_QUERY = + MultiDbJDBCConnection.FIND_NODES_BY_PARENTID_CQ_QUERY + .replaceFirst("select", + "select /*+ INDEX(I JCR_IDX_MITEM_PARENT_ID) INDEX(P JCR_IDX_MITEM_PARENT_ID) INDEX(V JCR_IDX_MVALUE_PROPERTY)*/"); + + protected static final String FIND_PROPERTIES_BY_PARENTID_CQ_QUERY = + MultiDbJDBCConnection.FIND_PROPERTIES_BY_PARENTID_CQ_QUERY.replaceFirst("select", + "select /*+ INDEX(I JCR_IDX_MITEM_PARENT_ID) INDEX(V JCR_IDX_MVALUE_PROPERTY)*/"); + /** * Oracle Multidatabase JDBC Connection constructor. * @@ -69,15 +78,7 @@ { super.prepareQueries(); - FIND_NODES_BY_PARENTID_CQ = - "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_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"; + FIND_NODES_BY_PARENTID_CQ = FIND_NODES_BY_PARENTID_CQ_QUERY; + FIND_PROPERTIES_BY_PARENTID_CQ = FIND_PROPERTIES_BY_PARENTID_CQ_QUERY; } } Modified: 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 2010-03-19 17:45:28 UTC (rev 2087) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/OracleSingleDbJDBCConnection.java 2010-03-20 07:02:33 UTC (rev 2088) @@ -32,6 +32,15 @@ public class OracleSingleDbJDBCConnection extends SingleDbJDBCConnection { + protected static final String FIND_NODES_BY_PARENTID_CQ_QUERY = + SingleDbJDBCConnection.FIND_NODES_BY_PARENTID_CQ_QUERY + .replaceFirst("select", + "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(P JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/"); + + protected static final String FIND_PROPERTIES_BY_PARENTID_CQ_QUERY = + SingleDbJDBCConnection.FIND_PROPERTIES_BY_PARENTID_CQ_QUERY.replaceFirst("select", + "select /*+ INDEX(I JCR_IDX_SITEM_PARENT_ID) INDEX(V JCR_IDX_SVALUE_PROPERTY)*/"); + /** * Oracle Singledatabase JDBC Connection constructor. * @@ -52,7 +61,7 @@ * @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 @@ -69,15 +78,7 @@ { 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"; + FIND_NODES_BY_PARENTID_CQ = FIND_NODES_BY_PARENTID_CQ_QUERY; + FIND_PROPERTIES_BY_PARENTID_CQ = FIND_PROPERTIES_BY_PARENTID_CQ_QUERY; } } 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 17:45:28 UTC (rev 2087) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/SingleDbJDBCConnection.java 2010-03-20 07:02:33 UTC (rev 2088) @@ -49,6 +49,18 @@ public class SingleDbJDBCConnection extends CQJDBCStorageConnection { + protected static final String FIND_NODES_BY_PARENTID_CQ_QUERY = + "select 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"; + + protected static final String FIND_PROPERTIES_BY_PARENTID_CQ_QUERY = + "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.I_CLASS=2 and I.CONTAINER_NAME=? and I.PARENT_ID=? order by I.NAME"; + protected PreparedStatement findItemById; protected PreparedStatement findItemByPath; @@ -204,12 +216,7 @@ FIND_NODES_BY_PARENTID = "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.*, 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_NODES_BY_PARENTID_CQ = FIND_NODES_BY_PARENTID_CQ_QUERY; FIND_NODE_MAIN_PROPERTIES_BY_PARENTID_CQ = "select I.NAME, V.DATA, V.ORDER_NUM" @@ -227,10 +234,7 @@ FIND_PROPERTIES_BY_PARENTID = "select * from JCR_SITEM" + " where I_CLASS=2 and CONTAINER_NAME=? and PARENT_ID=?" + " order by NAME"; - 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.I_CLASS=2 and I.CONTAINER_NAME=? and I.PARENT_ID=? order by I.NAME"; + FIND_PROPERTIES_BY_PARENTID_CQ = FIND_PROPERTIES_BY_PARENTID_CQ_QUERY; INSERT_NODE = "insert into JCR_SITEM(ID, PARENT_ID, NAME, CONTAINER_NAME, VERSION, I_CLASS, I_INDEX, N_ORDER_NUM) VALUES(?,?,?,?,?,"