Delete old espace versions

Delete old espace versions

  
Dear fellow community members,

I created a small application for our dev server to delete old espace versions. It doesn't make sence to keep all those old versions and I'm too lazy to delete them with 100 a time in servicecenter. I have found the entity that should be deleted containing the Espace in the System Espace.

When deleting the old espaces (except the ones used in solutions) I keep getting this Error: The DELETE statement conflicted with the REFERENCE constraint "OSFRK_ossys_App_Version_Module_Versi_OSSYS_ESPACE_VERSION_ESPACE_VERSION_ID". The conflict occurred in database "outsystems", table "dbo.OSSYS_APP_VERSION_MODULE_VERSI", column 'ESPACE_VERSION_ID'. The statement has been terminated.

Does anyone have an idea where to find this table? (I can find it in the DB on the server) (is it a non public entitiy in System?)

Kind regards,
Wouter Schrandt
Hi Wouter

I understand the driver for this small application, however, I strongly advise against manipulating the OutSystems metadata, due to the risk of data corruption, which could bring down your entire environment.

My suggestion is to get in touch with the OutSystems Technical Support, and request some guidance on how to properly tackle the old espace versions growth and limited delete functionality.

Cheers

Miguel Simões João


Wouter,

From the table names in your problem description, I'm guessing that you're using V7 of the platform. The APP tables don't exist in V6. So I can't help you out on this one but I wanted to share the cleanup procedure that we've put in place in V6. This has been validated by OS support. So if you contact them, they will surely help you.
We run the procedure below in our daily db cleanup. How we cleanup is as follows:
- we keep ALL versions up to 31 days
- we never delete versions used in a solution
- when we cleanup old version (> 31 days), we always keep the latest version of that day PER USER.

Why keep the latest version per user: just to have an idea who worked on a project that day. If you wan't to troubleshoot an issue, and it worked one day and not a few days later, you have a history of all the people having done something on the espace. Not to start blaming them of course, but to know which developers you might want to contact to help you finding the cause.

Procedure in V6:

PROCEDURE removeoldespaceversions

( days IN INTEGER DEFAULT 31)

IS

--

-- Purpose: Deletes espace versions older than x days

-- per espace, per day, per user, 1 version is kept

--

-- MODIFICATION HISTORY

-- Person Date Comments

-- --------- ------ -------------------------------------------

BEGIN

DELETE

FROM outsys_sys.ossys_espace_version Espace_Version

WHERE Espace_Version.ID in (

-- select all espace version older than x days

SELECT Espace_Version.Id

FROM outsys_sys.ossys_espace Espace,

outsys_sys.ossys_espace_version Espace_Version

WHERE Espace_Version.ESPACE_ID = Espace.ID

AND Espace.Version_Id <> Espace_Version.Id

AND Espace_Version.UPLOADED_DATE < (SysTimeStamp - 31)

MINUS

-- do not take espace versions used in a solution into account

SELECT distinct(Espace_Version_Id)

FROM outsys_sys.ossys_solution_version_ref

WHERE not Espace_Version_Id IS NULL

MINUS

-- do not take max espace version per day per user into account

SELECT max(Espace_Version.Id)

FROM outsys_sys.ossys_Espace Espace,

outsys_sys.ossys_espace_version Espace_Version

WHERE Espace_Version.ESPACE_ID = Espace.ID

AND Espace.Version_Id <> Espace_Version.Id

AND Espace_Version.UPLOADED_DATE < (SysTimeStamp - 31)

GROUP BY Espace.ID, trunc(uploaded_date), uploaded_by

);

END; -- Procedure


 
Thanks to the both of you,

I'll take a look into the idea of Kurt next week. Maybe this would be a nice feature for the platform itself too.... having some kind of option to use an automated cleanup which indeed keeps the version of each user too.

Regards
Wouter
Is there a updated version of this procedure? i'am looking for a way to clear old version on a SQL stack.
New version, now also takes LifeTime version into account. Written for Oracle, but I suppose it won't be that much different for SQL

PROCEDURE              removeoldespaceversions   ( days IN INTEGER DEFAULT 31)
   IS
--
-- Purpose: Deletes espace versions older than x days
--          per espace, per day, per user, 1 version is kept
--
-- MODIFICATION HISTORY
-- Date            Comments
-- ------          -------------------------------------------
-- 19/05/2016      Replaced the clause that would keep all module version by user/day
--                          by a new one that aims to keep a version by day over Year-1 and 
--                          a version each month for Year-2
 
BEGIN
    DELETE
    FROM outsys_sys.ossys_espace_version Espace_Version
    WHERE Espace_Version.ID in (
 
        -- select all espace version older than X days
        SELECT Espace_Version.Id
        FROM   outsys_sys.ossys_espace Espace,
               outsys_sys.ossys_espace_version Espace_Version
        WHERE  Espace_Version.ESPACE_ID = Espace.ID
          AND  Espace.Version_Id <> Espace_Version.Id
      AND  Espace.Pending_Version_Id is Null
          AND  Espace_Version.UPLOADED_DATE < (SysTimeStamp - days)
 
        MINUS
 
        -- do not take espace versions used in a SOLUTION into account
        SELECT distinct(Espace_Version_Id)
        FROM   outsys_sys.ossys_solution_version_ref
        WHERE  not Espace_Version_Id IS NULL
 
        MINUS
 
        -- do not take espace versions used in a APPLICATION version into account
        SELECT distinct(Espace_Version_Id)
        FROM   outsys_sys.ossys_app_version_module_versi
        WHERE  not Espace_Version_Id IS NULL
 
        MINUS
 
        -- on Year-1 do not take the last espace version per DAY into account
        SELECT max(Espace_Version.Id)
        FROM   outsys_sys.ossys_Espace Espace,
               outsys_sys.ossys_espace_version Espace_Version
        WHERE  Espace_Version.ESPACE_ID = Espace.ID
          AND  Espace.Version_Id <> Espace_Version.Id
          AND  Espace_Version.UPLOADED_DATE between (SysTimeStamp - 365 - days) and (SysTimeStamp - days) 
        GROUP BY Espace.ID, trunc(uploaded_date) 
 
        MINUS
 
        -- on Year-2 do not take the last espace version per MONTH into account
        SELECT max(Espace_Version.Id)
        FROM   outsys_sys.ossys_Espace Espace,
               outsys_sys.ossys_espace_version Espace_Version
        WHERE  Espace_Version.ESPACE_ID = Espace.ID
          AND  Espace.Version_Id <> Espace_Version.Id
          AND  Espace_Version.UPLOADED_DATE between (SysTimeStamp - 730 - days) and (SysTimeStamp - 365 - days) 
        GROUP BY Espace.ID, trunc(uploaded_date,'MONTH')
    );
 
END; -- Procedure
Hi Wouter,
Have a look at DBCleaner in the forge... I use it and have no issues.
https://www.outsystems.com/forge/component-details/423/DBCleaner/
Cheers
Gary