[opencms-dev] How to delete from DB deleted resources atworkplace

[a.westermann at alkacon.com]

Hi Juan Francicso,


I post one query you may use AT YOUR OWN RISK. It deletes all entries in 
the CMS_CONTENTS table that have an history entry (have been published 
before) but are not online any more (have  been deleted and published).


DELETE FROM CMS_CONTENTS WHERE CMS_CONTENTS.RESOURCE_ID IN (SELECT 
CMS_HISTORY_RESOURCES.RESOURCE_ID FROM CMS_HISTORY_RESOURCES WHERE 
CMS_HISTORY_RESOURCES.RESOURCE_ID NOT IN (SELECT 
CMS_ONLINE_RESOURCES.RESOURCE_ID FROM CMS_ONLINE_RESOURCES)) limit 100;

You may want to replace the "DELETE FROM " by a "SELECT * FROM". And 
also dump before any action to have a rollback. Also consider that the 
operation may be very expensive and slow down your server.

This will clean up a major part of space used by your db.

But also queries are needed for:

CMS_HISTORY_STRUCTURE
CMS_HISTORY_PRINCIPALS
CMS_HISTORY_PROJECTRESOURCES
CMS_HISTORY_PROPERTIES
CMS_HISTORY_PROPERTYDEF
CMS_HISTORY_RESOURCES

to have it clean. I just did not have the time for searching them. If 
you find some feel free to repost.



Kind Regards,
Achim.

-------------------
Achim Westermann

Alkacon Software GmbH  - The OpenCms Experts
http://www.alkacon.com - http://www.opencms.org



Juan Francisco Fernández wrote:
> We are using OpenCms 7.0.4. Upgrading to 7.5.0 version is not a possibility
> at moment (we have no time & personal resources before holidays, I hope to
> do it when we come back in September :)), but our data base is growing up,
> and lot of resources has been deleted from the workplace but are present in
> DB.
> 
> Thanks!
> 
> 
> -----Mensaje original-----
> De: opencms-dev-bounces at opencms.org [mailto:opencms-dev-bounces at opencms.org]
> En nombre de a.westermann at alkacon.com
> Enviado el: martes, 21 de julio de 2009 12:44
> Para: The OpenCms mailing list
> Asunto: Re: [opencms-dev] How to delete from DB deleted resources
> atworkplace
> 
> Hi Juan Francisco,
> 
> which OpenCms Version are you using? Some bugs / features related to
> history cleaning have been released in the OpenCms Version 7.5.0.
> 
> Also the queries will depend on the OpenCms Version.
> 
> 
> Kind Regards,
> Achim.
> 
> -------------------
> Achim Westermann
> 
> Alkacon Software GmbH  - The OpenCms Experts
> http://www.alkacon.com - http://www.opencms.org
> 
> 
> 
> Juan Francisco Fernández wrote:
>> Hello all,
>>
>> I'm having problems trying to delete files from history. When I go to
>> Administration -> File history -> Clear History, with "Clear versions of
>> deleted files" checked, I'm getting errors on previously deleted files,
> with
>> an Error! message in the workplace, and nothing in the opencms logs.
>>
>> So I'm thinking to delete them directly from the DB, is there any method
> to
>> do it? Would be enough clearing the cms_history_xxxxxx tables?
>>
>> Thanks in advance.
>>
>> Greentings!.
>>
>>
>> _______________________________________________
>> This mail is sent to you from the opencms-dev mailing list
>> To change your list options, or to unsubscribe from the list, please visit
>> http://lists.opencms.org/mailman/listinfo/opencms-dev
> 
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev
> 
> 
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev