All docs
This doc
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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; |
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
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; |