Author: sergiykarpenko Date: 2010-09-17 10:58:19 -0400 (Fri, 17 Sep 2010) New Revision: 3132 Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2v8.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mssql.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora-analyze.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sybase.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2v8.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.mssql.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora-analyze.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora.sql jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sybase.sql Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mysql.sql Log: EXOJCR-939: scripts added. All supported databases tested. Modified: jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java 2010-09-17 09:45:55 UTC (rev 3131) +++ jcr/trunk/exo.jcr.component.core/src/main/java/org/exoplatform/services/jcr/impl/util/jdbc/DBCleanerService.java 2010-09-17 14:58:19 UTC (rev 3132) @@ -180,10 +180,6 @@ { sqlPath = sqlPath + "jdbc.mssql.sql"; } - else if (dbDialect == DBConstants.DB_DIALECT_DERBY) - { - sqlPath = sqlPath + "jdbc.derby.sql"; - } else if (dbDialect == DBConstants.DB_DIALECT_DB2) { sqlPath = sqlPath + "jdbc.db2.sql"; @@ -196,10 +192,6 @@ { sqlPath = sqlPath + "jdbc.sybase.sql"; } - else if (dbDialect == DBConstants.DB_DIALECT_INGRES) - { - sqlPath = sqlPath + "jdbc.ingres.sql"; - } else if (dbDialect == DBConstants.DB_DIALECT_HSQLDB) { sqlPath = sqlPath + "jdbc.sql"; Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,3 @@ +DROP TABLE JCR_MREF; +DROP TABLE JCR_MVALUE; +DROP TABLE JCR_MITEM; Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2v8.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2v8.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.db2v8.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,3 @@ +DROP TABLE JCR_MREF; +DROP TABLE JCR_MVALUE; +DROP TABLE JCR_MITEM; Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mssql.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mssql.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mssql.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,3 @@ +DROP TABLE JCR_MREF; +DROP TABLE JCR_MVALUE; +DROP TABLE JCR_MITEM; \ No newline at end of file Modified: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mysql.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mysql.sql 2010-09-17 09:45:55 UTC (rev 3131) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.mysql.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -1,3 +1,3 @@ DROP TABLE JCR_MREF; DROP TABLE JCR_MVALUE; -DROP TABLE JCR_MITEM; +DROP TABLE JCR_MITEM; \ No newline at end of file Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora-analyze.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora-analyze.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora-analyze.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,90 @@ +/*$DELIMITER:/ */ +CREATE TABLE JCR_MCONTAINER( + VERSION VARCHAR2(96) NOT NULL, + CONSTRAINT JCR_PK_MCONTAINER PRIMARY KEY(VERSION) +) +/ +CREATE TABLE JCR_MITEM( + ID VARCHAR2(96) NOT NULL, + PARENT_ID VARCHAR2(96) NOT NULL, + NAME VARCHAR2(512) NOT NULL, + VERSION INTEGER NOT NULL, + I_CLASS INTEGER NOT NULL, + I_INDEX INTEGER NOT NULL, + N_ORDER_NUM INTEGER, + P_TYPE INTEGER, + P_MULTIVALUED INTEGER, + CONSTRAINT JCR_PK_MITEM PRIMARY KEY(ID), + CONSTRAINT JCR_FK_MITEM_PARENT FOREIGN KEY(PARENT_ID) REFERENCES JCR_MITEM(ID) + ) +/ +CREATE INDEX JCR_IDX_MITEM_PARENT_FK ON JCR_MITEM(PARENT_ID) +/ +CREATE UNIQUE INDEX JCR_IDX_MITEM_PARENT ON JCR_MITEM( PARENT_ID, NAME, I_INDEX, I_CLASS, VERSION DESC) +/ +CREATE UNIQUE INDEX JCR_IDX_MITEM_PARENT_NAME ON JCR_mITEM(I_CLASS, PARENT_ID, NAME, I_INDEX, VERSION DESC) +/ +CREATE UNIQUE INDEX JCR_IDX_MITEM_PARENT_ID ON JCR_MITEM(I_CLASS, PARENT_ID, ID, VERSION DESC) +/ +CREATE TABLE JCR_MVALUE( + ID INTEGER NOT NULL, + DATA BLOB, + ORDER_NUM INTEGER NOT NULL, + PROPERTY_ID VARCHAR(96) NOT NULL, + STORAGE_DESC VARCHAR(512), + CONSTRAINT JCR_PK_MVALUE PRIMARY KEY(ID), + CONSTRAINT JCR_FK_MVALUE_PROPERTY FOREIGN KEY(PROPERTY_ID) REFERENCES JCR_MITEM(ID) + ) +/ +CREATE UNIQUE INDEX JCR_IDX_MVALUE_PROPERTY ON JCR_MVALUE(PROPERTY_ID, ORDER_NUM) +/ +CREATE sequence JCR_MVALUE_SEQ +/ +CREATE OR REPLACE trigger BI_JCR_MVALUE + before insert on JCR_MVALUE + for each row +begin + SELECT JCR_MVALUE_SEQ.nextval INTO :NEW.ID FROM dual; +end; +/ +CREATE TABLE JCR_MREF( + NODE_ID VARCHAR(96) NOT NULL, + PROPERTY_ID VARCHAR(96) NOT NULL, + ORDER_NUM INTEGER NOT NULL, + CONSTRAINT JCR_PK_MREF PRIMARY KEY(NODE_ID, PROPERTY_ID, ORDER_NUM) +) +/ +CREATE UNIQUE INDEX JCR_IDX_MREF_PROPERTY ON JCR_MREF(PROPERTY_ID, ORDER_NUM) +/ + + + + + + +ANALYZE INDEX JCR_PK_SITEM COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MITEM_ID COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MITEM_PATH COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_PK_MNODE COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MNODE_PARENT COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MNODE_PARENTID COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_PK_MPROPERTY COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MPROPERTY_PARENT COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MPROPERTY_PARENTID COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_PK_MVALUE COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MVALUE_PROPERTY COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_PK_MREF COMPUTE STATISTICS +/ +ANALYZE INDEX JCR_IDX_MREF_PROPERTY COMPUTE STATISTICS +/ \ No newline at end of file Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.ora.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,6 @@ +/*$DELIMITER:/ */ +DROP TABLE JCR_MREF/ +DROP TABLE JCR_MVALUE/ +DROP TABLE JCR_MITEM/ + + Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sybase.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sybase.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-mjdbc.sybase.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,4 @@ +DROP TABLE JCR_MREF; +DROP TABLE JCR_MVALUE; +DROP TABLE JCR_MITEM; + Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,3 @@ +delete from JCR_SVALUE where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SREF where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SITEM where CONTAINER_NAME=?; \ No newline at end of file Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2v8.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2v8.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.db2v8.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,3 @@ +delete from JCR_SVALUE where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SREF where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SITEM where CONTAINER_NAME=?; \ No newline at end of file Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.mssql.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.mssql.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.mssql.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,3 @@ +delete from JCR_SVALUE where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SREF where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SITEM where CONTAINER_NAME=?; \ No newline at end of file Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora-analyze.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora-analyze.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora-analyze.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,5 @@ +/*$DELIMITER:/ */ +delete from JCR_SVALUE where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?)/ +delete from JCR_SREF where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?)/ +delete from JCR_SITEM where CONTAINER_NAME=?/ + Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.ora.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,4 @@ +/*$DELIMITER:/ */ +delete from JCR_SVALUE where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?)/ +delete from JCR_SREF where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?)/ +delete from JCR_SITEM where CONTAINER_NAME=?/ Added: jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sybase.sql =================================================================== --- jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sybase.sql (rev 0) +++ jcr/trunk/exo.jcr.component.core/src/main/resources/conf/storage/cleanup/jcr-sjdbc.sybase.sql 2010-09-17 14:58:19 UTC (rev 3132) @@ -0,0 +1,3 @@ +delete from JCR_SVALUE where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SVALUE.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SREF where exists(select * from JCR_SITEM where JCR_SITEM.ID=JCR_SREF.PROPERTY_ID and JCR_SITEM.CONTAINER_NAME=?); +delete from JCR_SITEM where CONTAINER_NAME=?;