[opencms-dev] How to delete from DB deleted resourcesatworkplace

[Juan Francisco Fernández]

I will try it first in our development server, so no problem if we lost
something. Also, I will research the other tables' queries with the one you
provide in mind, if I find a solution I will post it back here.

Thanks a lot Achim.

Greetings!

-----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 13:10
Para: The OpenCms mailing list
Asunto: Re: [opencms-dev] How to delete from DB deleted resourcesatworkplace

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

_______________________________________________
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