Page History

...

Code Block

language	sql
title	DB Types: H2, DB2, MySQL, MSSQL, Oracle and Postgresql
collapse	true

CREATE TABLE REG_LOG_IDS_TO_KEEP (
REG_LOG_ID INTEGER,
REG_TENANT_ID INTEGER
);

INSERT INTO REG_LOG_IDS_TO_KEEP (REG_LOG_ID, REG_TENANT_ID)
SELECT MAX(REG_LOG_ID) AS REG_LOG_ID, REG_TENANT_ID FROM REG_LOG GROUP BY REG_PATH, REG_TENANT_ID;

DELETE FROM REG_LOG WHERE REG_LOG_ID NOT IN (SELECT REG_LOG_ID FROM REG_LOG_IDS_TO_KEEP);
DROP TABLE REG_LOG_IDS_TO_KEEP;

DELETE FROM REG_LOG WHERE REG_ACTION = 7;

...

Code Block

language	sql
title	DB types: H2, MSSQL and Postgresql
collapse	true

CREATE TABLE TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID INTEGER);

-- Extract resource property (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
SELECT REG_PROPERTY_ID
FROM REG_RESOURCE_PROPERTY
WHERE REG_PATH_ID IN
    (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

-- Extract resource property (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
SELECT REG_PROPERTY_ID
FROM REG_RESOURCE_PROPERTY
WHERE REG_VERSION IN
    (SELECT REG_VERSION
        FROM REG_RESOURCE);

-- Drop the foreign key constraint --

ALTER TABLE REG_RESOURCE_PROPERTY DROP CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID;

-- delete all unwanted REG_RESOURCE_PROPERTY entries --

DELETE
FROM REG_RESOURCE_PROPERTY
WHERE REG_PROPERTY_ID NOT IN
    (SELECT REG_PROPERTY_ID
    FROM TEMP_REG_RESOURCE_PROPERTY_ID);

-- delete all unwanted REG_PROPERTY entries --

DELETE
FROM REG_PROPERTY
WHERE REG_ID NOT IN
    (SELECT REG_PROPERTY_ID
    FROM TEMP_REG_RESOURCE_PROPERTY_ID);

-- Insert back the foreign key constraint --

ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);

-- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_PROPERTY_ID;

Code Block

language	sql
title	DB types: MySQL
collapse	true

CREATE TABLE TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID INTEGER);

-- Extract resource property (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
SELECT REG_PROPERTY_ID
FROM REG_RESOURCE_PROPERTY
WHERE REG_PATH_ID IN
    (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

-- Extract resource property (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
SELECT REG_PROPERTY_ID
FROM REG_RESOURCE_PROPERTY
WHERE REG_VERSION IN
    (SELECT REG_VERSION
        FROM REG_RESOURCE);

-- Drop the foreign key constraint --

ALTER TABLE REG_RESOURCE_PROPERTY DROP FOREIGN KEY REG_RESOURCE_PROPERTY_FK_BY_TAG_ID;

-- delete all unwanted REG_RESOURCE_PROPERTY entries --

DELETE
FROM REG_RESOURCE_PROPERTY
WHERE REG_PROPERTY_ID NOT IN
    (SELECT REG_PROPERTY_ID
    FROM TEMP_REG_RESOURCE_PROPERTY_ID);

-- delete all unwanted REG_PROPERTY entries --

DELETE
FROM REG_PROPERTY
WHERE REG_ID NOT IN
    (SELECT REG_PROPERTY_ID
    FROM TEMP_REG_RESOURCE_PROPERTY_ID);

-- Insert back the foreign key constraint --

ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);

-- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_PROPERTY_ID;

Code Block

language	sql
title	DB types: DB2 and Oracle
collapse	true

CREATE TABLE TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID INTEGER);

-- Extract resource property (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
SELECT REG_PROPERTY_ID
FROM REG_RESOURCE_PROPERTY
WHERE REG_PATH_ID IN
    (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

-- Extract resource property (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
SELECT REG_PROPERTY_ID
FROM REG_RESOURCE_PROPERTY
WHERE REG_VERSION IN
    (SELECT REG_VERSION
        FROM REG_RESOURCE);

-- delete all unwanted REG_RESOURCE_PROPERTY entries --

DELETE
FROM REG_RESOURCE_PROPERTY
WHERE REG_PROPERTY_ID NOT IN
    (SELECT REG_PROPERTY_ID
    FROM TEMP_REG_RESOURCE_PROPERTY_ID);

-- delete all unwanted REG_PROPERTY entries --

DELETE
FROM REG_PROPERTY
WHERE REG_ID NOT IN
    (SELECT REG_PROPERTY_ID
    FROM TEMP_REG_RESOURCE_PROPERTY_ID);

-- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_PROPERTY_ID;

REG_TAG and REG_RESOURCE_TAG Table Cleanup

Code Block

language	sql
title	DB types: H2, MSSQL and Postgresql
collapse	true

CREATE TABLE TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID INTEGER);

-- Extract resource tag (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
SELECT REG_TAG_ID
FROM REG_RESOURCE_TAG
WHERE REG_PATH_ID IN
    (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

-- Extract resource tag (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
SELECT REG_TAG_ID
FROM REG_RESOURCE_TAG
WHERE REG_VERSION IN
    (SELECT REG_VERSION
        FROM REG_RESOURCE);

-- Remove the foreign key constraint --

ALTER TABLE REG_RESOURCE_TAG DROP CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID;

-- delete all unwanted REG_RESOURCE_TAG entries --

DELETE
FROM REG_RESOURCE_TAG
WHERE REG_TAG_ID NOT IN
    (SELECT REG_TAG_ID
    FROM TEMP_REG_RESOURCE_TAG_ID);

-- delete all unwanted REG_TAG entries --

DELETE
FROM REG_TAG
WHERE REG_ID NOT IN
    (SELECT REG_TAG_ID
    FROM TEMP_REG_RESOURCE_TAG_ID);

-- add the foreign key constraint back --

ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);

-- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_TAG_ID;

Code Block

language	sql
title	DB types: MySQL
collapse	true

CREATE TABLE TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID INTEGER);

-- Extract resource tag (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
SELECT REG_TAG_ID
FROM REG_RESOURCE_TAG
WHERE REG_PATH_ID IN
    (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

-- Extract resource tag (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
SELECT REG_TAG_ID
FROM REG_RESOURCE_TAG
WHERE REG_VERSION IN
    (SELECT REG_VERSION
        FROM REG_RESOURCE);

-- Remove the foreign key constraint --

ALTER TABLE REG_RESOURCE_TAG DROP FOREIGN KEY REG_RESOURCE_TAG_FK_BY_TAG_ID;

-- delete all unwanted REG_RESOURCE_TAG entries --

DELETE
FROM REG_RESOURCE_TAG
WHERE REG_TAG_ID NOT IN
    (SELECT REG_TAG_ID
    FROM TEMP_REG_RESOURCE_TAG_ID);

-- delete all unwanted REG_TAG entries --

DELETE
FROM REG_TAG
WHERE REG_ID NOT IN
    (SELECT REG_TAG_ID
    FROM TEMP_REG_RESOURCE_TAG_ID);

-- add the foreign key constraint back --

ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);

-- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_TAG_ID;

Code Block

language	sql
title	DB types: DB2 and Oracle
collapse	true

CREATE TABLE TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID INTEGER);

-- Extract resource tag (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
SELECT REG_TAG_ID
FROM REG_RESOURCE_TAG
WHERE REG_PATH_ID IN
    (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

-- Extract resource tag (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_TAG_ID(REG_TAG_ID)
SELECT REG_TAG_ID
FROM REG_RESOURCE_TAG
WHERE REG_VERSION IN
    (SELECT REG_VERSION
        FROM REG_RESOURCE);

-- delete all unwanted REG_RESOURCE_TAG entries --

DELETE
FROM REG_RESOURCE_TAG
WHERE REG_TAG_ID NOT IN
    (SELECT REG_TAG_ID
    FROM TEMP_REG_RESOURCE_TAG_ID);

-- delete all unwanted REG_TAG entries --

DELETE
FROM REG_TAG
WHERE REG_ID NOT IN
    (SELECT REG_TAG_ID
    FROM TEMP_REG_RESOURCE_TAG_ID);

-- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_TAG_ID;

Versions Compared

Old Version 7

New Version Current

Key

REG_TAG and REG_RESOURCE_TAG Table Cleanup