Author: tolusha Date: 2012-01-11 04:17:07 -0500 (Wed, 11 Jan 2012) New Revision: 5437 Modified: jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLMultiDbJDBCConnection.java jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLSingleDbJDBCConnection.java jcr/branches/1.15.x/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java Log: EXOJCR-1697: Improve SQL access Modified: jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java =================================================================== --- jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java 2012-01-11 09:16:01 UTC (rev 5436) +++ jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java 2012-01-11 09:17:07 UTC (rev 5437) @@ -128,6 +128,7 @@ + "' and NOT EXISTS(select * from JCR_SITEM P where P.ID = I.PARENT_ID)", new String[]{ DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME, DBConstants.COLUMN_CLASS}, "Items that do not have parent nodes", InspectionStatus.ERR)); + queries.add(new InspectionQuery(jdbcDataContainer.multiDb ? "select * from JCR_MITEM N where N.I_CLASS=1 and NOT EXISTS " + "(select * from JCR_MITEM P where P.I_CLASS=2 and P.PARENT_ID=N.ID)" @@ -136,6 +137,7 @@ + "where P.I_CLASS=2 and P.PARENT_ID=N.ID and P.CONTAINER_NAME='" + jdbcDataContainer.containerName + "')", new String[]{DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME}, "Nodes that do not have at least one property", InspectionStatus.ERR)); + queries.add(new InspectionQuery(jdbcDataContainer.multiDb ? "select * from JCR_MVALUE V where NOT EXISTS(select * from JCR_MITEM P " + "where V.PROPERTY_ID = P.ID and P.I_CLASS=2)" @@ -144,6 +146,7 @@ + jdbcDataContainer.containerName + "' and V.PROPERTY_ID = P.ID and P.I_CLASS=2)", new String[]{ DBConstants.COLUMN_ID, DBConstants.COLUMN_VPROPERTY_ID}, "All value records that has not owner-property record", InspectionStatus.ERR)); + queries.add(new InspectionQuery(jdbcDataContainer.multiDb ? "select * from JCR_MITEM P where P.I_CLASS=2 and NOT EXISTS( select * from JCR_MVALUE V " + "where V.PROPERTY_ID=P.ID)" : "select * from JCR_SITEM P where P.CONTAINER_NAME='" @@ -184,6 +187,7 @@ } queries.add(new InspectionQuery(statement, new String[]{DBConstants.COLUMN_ID}, "Incorrect JCR_VALUE records", InspectionStatus.ERR)); + queries.add(new InspectionQuery(jdbcDataContainer.multiDb ? "select * from JCR_MITEM P where P.P_TYPE=9 and NOT EXISTS " + "(select * from JCR_MREF R where P.ID=R.PROPERTY_ID)" @@ -210,6 +214,21 @@ DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME}, "An item is its own parent.", InspectionStatus.ERR)); + // Several versions of same item + queries + .add(new InspectionQuery( + jdbcDataContainer.multiDb + ? "select * from JCR_MITEM I where EXISTS (select count(VERSION) from JCR_MITEM J" + + " WHERE I.PARENT_ID = J.PARENT_ID AND I.NAME = J.NAME and I.I_INDEX = J.I_INDEX and I.I_CLASS = J.I_CLASS" + + " GROUP BY PARENT_ID, NAME, I_INDEX, I_CLASS HAVING count(VERSION) > 1)" + : "select * from JCR_SITEM I where I.CONTAINER_NAME='" + jdbcDataContainer.containerName + "' and" + + " EXISTS (select count(VERSION) from JCR_SITEM J WHERE I.CONTAINER_NAME = J.CONTAINER_NAME and" + + " I.PARENT_ID = J.PARENT_ID AND I.NAME = J.NAME and I.I_INDEX = J.I_INDEX and I.I_CLASS = J.I_CLASS" + + " GROUP BY CONTAINER_NAME, PARENT_ID, NAME, I_INDEX, I_CLASS HAVING count(VERSION) > 1)", new String[]{ + DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME, DBConstants.COLUMN_VERSION, + DBConstants.COLUMN_CLASS, DBConstants.COLUMN_INDEX}, "Several versions of same item.", + InspectionStatus.ERR)); + // using existing DataSource to get a JDBC Connection. Connection jdbcConn = jdbcDataContainer.getConnectionFactory().getJdbcConnection(); Modified: jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLMultiDbJDBCConnection.java =================================================================== --- jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLMultiDbJDBCConnection.java 2012-01-11 09:16:01 UTC (rev 5436) +++ jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLMultiDbJDBCConnection.java 2012-01-11 09:17:07 UTC (rev 5437) @@ -84,6 +84,8 @@ + " join (select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_INDEX, I.N_ORDER_NUM from JCR_MITEM I force index(PRIMARY)" + " where I.I_CLASS=1 AND I.ID > ? order by I.ID LIMIT ? OFFSET ?) J on P.PARENT_ID = J.ID" + " where P.I_CLASS=2 and V.PROPERTY_ID=P.ID order by J.ID"; + + FIND_ITEM_BY_NAME = "select * from JCR_MITEM where PARENT_ID=? and NAME=? and I_INDEX=? order by I_CLASS"; } /** Modified: jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLSingleDbJDBCConnection.java =================================================================== --- jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLSingleDbJDBCConnection.java 2012-01-11 09:16:01 UTC (rev 5436) +++ jcr/branches/1.15.x/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/optimisation/db/MySQLSingleDbJDBCConnection.java 2012-01-11 09:17:07 UTC (rev 5437) @@ -84,6 +84,11 @@ + " join (select I.ID, I.PARENT_ID, I.NAME, I.VERSION, I.I_INDEX, I.N_ORDER_NUM from JCR_SITEM I force index(PRIMARY)" + " where I.CONTAINER_NAME=? AND I.I_CLASS=1 AND I.ID > ? order by I.ID LIMIT ? OFFSET ?) J on P.PARENT_ID = J.ID" + " where P.I_CLASS=2 and P.CONTAINER_NAME=? and V.PROPERTY_ID=P.ID order by J.ID"; + + FIND_ITEM_BY_NAME = + "select * from JCR_SITEM" + + " where CONTAINER_NAME=? and PARENT_ID=? and NAME=? and I_INDEX=?" + + " order by I_CLASS"; } /** Modified: jcr/branches/1.15.x/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java =================================================================== --- jcr/branches/1.15.x/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java 2012-01-11 09:16:01 UTC (rev 5436) +++ jcr/branches/1.15.x/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java 2012-01-11 09:17:07 UTC (rev 5437) @@ -365,6 +365,73 @@ /** * Usecase: property doens't have have parent node. */ + public void testDBUsecasesSeveralVersionsOfSameItemSingleDB() throws Exception + { + checkSeveralVersionsOfSameItem(helper.createRepository(container, false, false)); + } + + /** + * Usecase: property doens't have have parent node. + */ + public void testDBUsecasesSeveralVersionsOfSameItemMultiDB() throws Exception + { + checkSeveralVersionsOfSameItem(helper.createRepository(container, true, false)); + } + + private void checkSeveralVersionsOfSameItem(ManageableRepository repository) throws Exception + { + // create repository and add property + SessionImpl session = + (SessionImpl)repository.login(credentials, repository.getConfiguration().getSystemWorkspaceName()); + NodeImpl node = (NodeImpl)session.getRootNode().addNode("testNode"); + PropertyImpl prop = (PropertyImpl)node.setProperty("prop", "test"); + session.save(); + session.logout(); + + // repository is consistent + checkController = new RepositoryCheckController(repository); + assertTrue(checkController.checkRepositoryDataBaseConsistency().startsWith("Repository data is consistent")); + checkController.getLastLogFile().delete(); + + WorkspaceEntry wsEntry = repository.getConfiguration().getWorkspaceEntries().get(0); + boolean isMultiDb = wsEntry.getContainer().getParameterBoolean(JDBCWorkspaceDataContainer.MULTIDB); + + // change ITEM table + String sourceName = wsEntry.getContainer().getParameterValue(JDBCWorkspaceDataContainer.SOURCE_NAME); + + Connection conn = ((DataSource)new InitialContext().lookup(sourceName)).getConnection(); + + // add another item with new persisted version + if (isMultiDb) + { + String propId = IdGenerator.generate(); + conn.prepareStatement( + "INSERT INTO JCR_MITEM VALUES ('" + propId + "','" + prop.getParentIdentifier() + + "','[]prop',1,2,1,NULL,1,FALSE)").execute(); + conn.prepareStatement("ALTER TABLE JCR_MVALUE DROP CONSTRAINT JCR_PK_MVALUE").execute(); + conn.prepareStatement("INSERT INTO JCR_MVALUE VALUES ('100','data','1','" + propId + "',NULL)").execute(); + } + else + { + String propId = wsEntry.getName() + IdGenerator.generate(); + conn.prepareStatement( + "INSERT INTO JCR_SITEM VALUES ('" + propId + "','" + wsEntry.getName() + prop.getParentIdentifier() + + "','[]prop',1,'" + wsEntry.getName() + "',2,1,NULL,1,FALSE)").execute(); + conn.prepareStatement("ALTER TABLE JCR_SVALUE DROP CONSTRAINT JCR_PK_SVALUE").execute(); + conn.prepareStatement("INSERT INTO JCR_SVALUE VALUES ('100','data','1','" + propId + "',NULL)").execute(); + } + + conn.commit(); + conn.close(); + + // repository is inconsistent + assertTrue(checkController.checkRepositoryDataBaseConsistency().startsWith("Repository data is inconsistent")); + checkController.getLastLogFile().delete(); + } + + /** + * Usecase: property doens't have have parent node. + */ public void testDBUsecasesPropertyWithoutParentSingleDB() throws Exception { checkDBUsecasesPropertyWithoutParent(helper.createRepository(container, false, false));