Author: zavizionov Date: 2011-11-08 03:45:44 -0500 (Tue, 08 Nov 2011) New Revision: 5170 Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java jcr/trunk/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java Log: EXOJCR-1613 TestRepositoryCheckController failed on Oracle DB Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java 2011-11-08 07:47:11 UTC (rev 5169) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/storage/jdbc/JDBCWorkspaceDataContainerChecker.java 2011-11-08 08:45:44 UTC (rev 5170) @@ -122,68 +122,79 @@ // preload queries queries.add(new InspectionQuery(jdbcDataContainer.multiDb - ? "select * from JCR_MITEM as I where NOT EXISTS(select * from JCR_MITEM AS P where P.ID = I.PARENT_ID)" - : "select * from JCR_SITEM as I where I.CONTAINER_NAME='" + jdbcDataContainer.containerName - + "' and NOT EXISTS(select * from JCR_SITEM AS P where P.ID = I.PARENT_ID)", new String[]{ + ? "select * from JCR_MITEM I where NOT EXISTS(select * from JCR_MITEM P where P.ID = I.PARENT_ID)" + : "select * from JCR_SITEM I where I.CONTAINER_NAME='" + jdbcDataContainer.containerName + + "' 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 as N where N.I_CLASS=1 and NOT EXISTS (select * from JCR_MITEM AS P where P.I_CLASS=2 and P.PARENT_ID=N.ID)" - : "select * from JCR_SITEM as N where N.CONTAINER_NAME='" + ? "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)" + : "select * from JCR_SITEM N where N.CONTAINER_NAME='" + jdbcDataContainer.containerName - + "' and N.I_CLASS=1 and NOT EXISTS (select * from JCR_SITEM AS P where P.I_CLASS=2 and P.PARENT_ID=N.ID and P.CONTAINER_NAME='" + + "' and N.I_CLASS=1 and NOT EXISTS (select * from JCR_SITEM P 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 as V where NOT EXISTS(select * from JCR_MITEM as P where V.PROPERTY_ID = P.ID and P.I_CLASS=2)" - : "select V.* from JCR_SVALUE as V, JCR_SITEM as I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" + ? "select * from JCR_MVALUE V where NOT EXISTS(select * from JCR_MITEM P where V.PROPERTY_ID = P.ID and P.I_CLASS=2)" + : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" + jdbcDataContainer.containerName - + "' and NOT EXISTS(select * from JCR_SITEM as P where P.CONTAINER_NAME='" + + "' and NOT EXISTS(select * from JCR_SITEM P where P.CONTAINER_NAME='" + 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 as P where P.I_CLASS=2 and NOT EXISTS( select * from JCR_MVALUE as V where V.PROPERTY_ID=P.ID)" - : "select * from JCR_SITEM as P where P.CONTAINER_NAME='" + jdbcDataContainer.containerName - + "' and P.I_CLASS=2 and NOT EXISTS( select * from JCR_SVALUE as V where V.PROPERTY_ID=P.ID)", + ? "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='" + jdbcDataContainer.containerName + + "' and P.I_CLASS=2 and NOT EXISTS( select * from JCR_SVALUE V where V.PROPERTY_ID=P.ID)", new String[]{DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME}, "All properties that have not value record.", InspectionStatus.WARN)); + + // The differences in the queries by DB dialect. + // Oracle doesn't work correct with default query because empty value stored as null value. + String statement; + if (jdbcDataContainer.dbDialect.equals(DBConstants.DB_DIALECT_SYBASE)) { + statement = jdbcDataContainer.multiDb + ? "select * from JCR_MVALUE where (STORAGE_DESC is null and DATA like null) or (STORAGE_DESC is not null and not DATA like null)" + : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" + + jdbcDataContainer.containerName + + "' AND ((STORAGE_DESC is null and DATA like null) or (STORAGE_DESC is not null and not DATA like null))"; + } else if (jdbcDataContainer.dbDialect.equals(DBConstants.DB_DIALECT_ORACLE) || jdbcDataContainer.dbDialect.equals(DBConstants.DB_DIALECT_ORACLEOCI)) { + statement = jdbcDataContainer.multiDb + ? "select * from JCR_MVALUE where (STORAGE_DESC is not null and DATA is not null)" + : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" + + jdbcDataContainer.containerName + + "' AND (STORAGE_DESC is not null and DATA is not null)"; + } else { + statement = jdbcDataContainer.multiDb + ? "select * from JCR_MVALUE where (STORAGE_DESC is null and DATA is null) or (STORAGE_DESC is not null and DATA is not null)" + : "select V.* from JCR_SVALUE V, JCR_SITEM I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" + + jdbcDataContainer.containerName + + "' AND ((STORAGE_DESC is null and DATA is null) or (STORAGE_DESC is not null and DATA is not null))"; + } queries - .add(new InspectionQuery( - jdbcDataContainer.dbDialect.equals(DBConstants.DB_DIALECT_SYBASE) - ? jdbcDataContainer.multiDb - ? "select * from JCR_MVALUE where (STORAGE_DESC is null and DATA like null) or (STORAGE_DESC is not null and not DATA like null)" - : "select V.* from JCR_SVALUE as V, JCR_SITEM as I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" - + jdbcDataContainer.containerName - + "' AND ((STORAGE_DESC is null and DATA like null) or (STORAGE_DESC is not null and not DATA like null))" - : jdbcDataContainer.multiDb - ? "select * from JCR_MVALUE where (STORAGE_DESC is null and DATA is null) or (STORAGE_DESC is not null and DATA is not null)" - : "select V.* from JCR_SVALUE as V, JCR_SITEM as I where V.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" - + jdbcDataContainer.containerName - + "' AND ((STORAGE_DESC is null and DATA is null) or (STORAGE_DESC is not null and DATA is not null))", - new String[]{DBConstants.COLUMN_ID}, "Incorrect JCR_VALUE records", InspectionStatus.ERR)); + .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 AS P where P.P_TYPE=9 and NOT EXISTS( select * from JCR_MREF AS R where P.ID=R.PROPERTY_ID)" - : "select * from JCR_SITEM AS P where P.CONTAINER_NAME='" + jdbcDataContainer.containerName - + "' and P.P_TYPE=9 and NOT EXISTS( select * from JCR_SREF AS R where P.ID=R.PROPERTY_ID)", + ? "select * from JCR_MITEM P where P.P_TYPE=9 and NOT EXISTS( select * from JCR_MREF R where P.ID=R.PROPERTY_ID)" + : "select * from JCR_SITEM P where P.CONTAINER_NAME='" + jdbcDataContainer.containerName + + "' and P.P_TYPE=9 and NOT EXISTS( select * from JCR_SREF R where P.ID=R.PROPERTY_ID)", new String[]{DBConstants.COLUMN_ID, DBConstants.COLUMN_PARENTID, DBConstants.COLUMN_NAME}, "Reference properties without reference records", InspectionStatus.ERR)); // properties can refer to missing node. It is possible to perform this usecase via JCR API with no exceptions queries.add(new InspectionQuery(jdbcDataContainer.multiDb - ? "select * from JCR_MREF AS R where NOT EXISTS(select * from JCR_MITEM AS N where R.NODE_ID=N.ID)" - : "select * from JCR_SREF AS R, JCR_SITEM as I where R.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" + ? "select * from JCR_MREF R where NOT EXISTS(select * from JCR_MITEM N where R.NODE_ID=N.ID)" + : "select * from JCR_SREF R, JCR_SITEM I where R.PROPERTY_ID = I.ID and I.CONTAINER_NAME='" + jdbcDataContainer.containerName - + "' and NOT EXISTS(select * from JCR_SITEM AS N where N.CONTAINER_NAME='" + + "' and NOT EXISTS(select * from JCR_SITEM N where N.CONTAINER_NAME='" + jdbcDataContainer.containerName + "' and R.NODE_ID=N.ID)", new String[]{"NODE_ID", "PROPERTY_ID", DBConstants.COLUMN_VORDERNUM}, "Reference records that linked to unexisted nodes. Can be normal for some usecases.", InspectionStatus.WARN)); @@ -311,7 +322,7 @@ connection .prepareStatement(jdbcDataContainer.multiDb ? "SELECT PROPERTY_ID, ORDER_NUM, STORAGE_DESC from JCR_MVALUE where STORAGE_DESC is not null" - : "SELECT V.PROPERTY_ID, V.ORDER_NUM, V.STORAGE_DESC from JCR_SVALUE as V, JCR_SITEM as I where I.CONTAINER_NAME='" + : "SELECT V.PROPERTY_ID, V.ORDER_NUM, V.STORAGE_DESC from JCR_SVALUE V, JCR_SITEM I where I.CONTAINER_NAME='" + jdbcDataContainer.containerName + "' and V.PROPERTY_ID = I.ID and STORAGE_DESC is not null"); resultSet = st.executeQuery(); Modified: jcr/trunk/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java 2011-11-08 07:47:11 UTC (rev 5169) +++ jcr/trunk/exo.jcr.component.core/src/test/java/org/exoplatform/services/jcr/impl/TestRepositoryCheckController.java 2011-11-08 08:45:44 UTC (rev 5170) @@ -73,10 +73,9 @@ public void testDB() { String result = checkController.checkRepositoryDataConsistency(new DataStorage[]{DataStorage.DB}); - assertTrue(result.equals("Repository data is consistent. See full report by path " - + checkController.getLastLogFile().getAbsolutePath()) - || result.equals("Repository data is consistent, except some warnings. See full report by path " - + checkController.getLastLogFile().getAbsolutePath())); + assertNotNull(result); + assertTrue("Repository data is not consistent, result: " + result, result + .startsWith("Repository data is consistent")); } public void testValueStorage() throws Exception @@ -91,8 +90,9 @@ root.save(); String result = checkController.checkRepositoryDataConsistency(new DataStorage[]{DataStorage.VALUE_STORAGE}); - assertEquals("Repository data is consistent. See full report by path " - + checkController.getLastLogFile().getAbsolutePath(), result); + assertNotNull(result); + assertTrue("Repository data is not consistent, result: " + result, result + .startsWith("Repository data is consistent")); } finally { @@ -104,8 +104,9 @@ public void testSearchIndex() { String result = checkController.checkRepositoryDataConsistency(new DataStorage[]{DataStorage.LUCENE_INDEX}); - assertEquals("Repository data is consistent. See full report by path " - + checkController.getLastLogFile().getAbsolutePath(), result); + assertNotNull(result); + assertTrue("Repository data is not consistent, result: " + result, result + .startsWith("Repository data is consistent")); } public void testAll() @@ -113,9 +114,8 @@ String result = checkController.checkRepositoryDataConsistency(new DataStorage[]{DataStorage.DB, DataStorage.VALUE_STORAGE, DataStorage.LUCENE_INDEX}); - assertTrue(result.equals("Repository data is consistent. See full report by path " - + checkController.getLastLogFile().getAbsolutePath()) - || result.equals("Repository data is consistent, except some warnings. See full report by path " - + checkController.getLastLogFile().getAbsolutePath())); + assertNotNull(result); + assertTrue("Repository data is not consistent, result: " + result, result + .startsWith("Repository data is consistent")); } }