How to delete all resources and keywords?

[Vasik Greif]

Hi, how can I delete all resources and keywords I added during my
testing? I followed this thread:
http://groups.google.com/group/resourcespace/browse_thread/thread/fdf7749a042707ad
, I runned pages/tools/database_prune.php, when I do clear search as
admin, I don't get any results, but I still have some resources left
in filestore, some keywords left in db and all the metadump.xml files
are still in filestore.

Any ideas?
Thanks

Vasik

[Paul Manno]

If you want to delete all resources, add them all to a collection, then delete all resources in that collection.

Keywords, I'm pretty sure, will be taken care of if you reindex after you delete using the reindex.php in pages/tools/reindex.php

Paul

--
You received this message because you are subscribed to the Google Groups "ResourceSpace" group.
To post to this group, send email to resour...@googlegroups.com.
To unsubscribe from this group, send email to resourcespac...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/resourcespace?hl=en.

[Vasik Greif]

Thanks for reply. The problem is, I tried it that way already - I
added all the resources into collection, deleted them. I runned
database_prune.php and now reindex.php. On clear search in RS I don't
get any results. But still, there are some files in the filestore, all
of the metadump.xml files, and there are 6 pages of keywords in db
left.

Am I missing something? I spent quite a lot of time with RS setup, and
now just need to delete all the testing resources to be able to start
using RS regularly.
Thanks
Vasik

On Nov 2, 2:54 pm, Paul Manno <pgma...@gmail.com> wrote:
> If you want to delete all resources, add them all to a collection, then
> delete all resources in that collection.
>
> Keywords, I'm pretty sure, will be taken care of if you reindex after you
> delete using the reindex.php in pages/tools/reindex.php
>
> Paul
>
>
>
>
>
>
>
> On Wed, Nov 2, 2011 at 7:50 AM, Vasik Greif <vaclav.gr...@gmail.com> wrote:
> > Hi, how can I delete all resources and keywords I added during my
> > testing? I followed this thread:
>

> >http://groups.google.com/group/resourcespace/browse_thread/thread/fdf...

[Paul Manno]

Ah.  Yeah, I have seen an issue out there where the directory, and some files, still remain after deleting everything; don't know if there will be a patch to address this.  If the filestore does not contain any resources that you want, you could always delete and recreate the directory, making sure that the apache process user is the owner (i.e. www-data/apache/iis_usr owns the folder).

I have to make clear that if you delete the filestore, you'll lose ALL resources, so if this was just a testing install and you want to start fresh, you can safely delete the filestore and start over.  For that matter, you could even drop and recreate the database but ONLY if you are sure there are no resources you wish to keep.

Paul

[Paul Manno]

I should add, there are some settings stored in the database, so if you've tweaked your test install and you have it configured just as you want it to be, you might not want to drop the database

[Vasik Greif]

Thanks for reply. Well, this seems like a major bug to me - when admin
deletes a resource, it really HAS to be deleted.

Ie., I would have thousands of photos in RS in next months, and I will
need to download the old ones in zip and delete them after that on a
regular basis - there's no way to check if the resources were really
deleted. It get's even worse with keywords - as RS is search only
based, user starts typing a keyword, gets suggestion, and expects that
some resource with the keyword exists. It's impossible to have things
organized, if delete resources doesn't work 100%.

Any ideas on this?

[Paul Manno]

I know that there is a setting in config.default.php dealing with what "delete" means (i.e. should the resource actually be deleted, or just marked as deleted in the database).  I have not tested it myself, but I think that if you choose to set it so that the resource is actually deleted, the original file is deleted, but perhaps not the previews and perhaps not the metadump file.  Someone else here has commented on this, and complained about it, and perhaps even issued a patch file for it, I'm not sure.

Perhaps Tom or Dan could give more advice here.

[Vasik Greif]

Anyone from the developers team on this one?

I found this thread:
http://groups.google.com/group/resourcespace/browse_thread/thread/942dd380d85fa128/ee1a211df16f42e6?lnk=gst&q=deleting+resources#ee1a211df16f42e6
, which should solve the metadump.xml problem, but it doesn't solve
the problem that keywords are not being deleted.

[Jeff Harmon]

Try tools/database_prune.php periodically (cron?) to eliminate orphaned keywords while we work on better clean up.

Yours,
Jeff

--
Jeff Harmon
Chief Executive Officer
Colorhythm LLC
http://www.colorhythm.com

Main Office: +1 415-399-9921
Fax: +1 253-399-9928
Mobile: +1 510-710-9590

jha...@colorhythm.com

[Dan Huby]

Hi Vasik,

database_prune.php is just a 'cleanup' script that removes orphaned
rows (that really shouldn't be there anyway).

Even if you remove all resources, the user resource templates will
still be there so there will be some keywords remaining.

To remove all resource data from your system, drop the following
tables. ResourceSpace will automatically recreate them - doing it this
way resets the auto-increment counters so your first new resource with
have ID 1.

resource
resource_data
resource_keyword
resource_alt_files
resource_custom_access
resource_dimensions
resource_log
resource_related
keyword

You could also drop the collection and collection_* tables if you want
to clear those up too.

Hope that's useful.

Dan

[Allison Stec]

With people often testing this software before implementation I think this information would be nice to add to the wiki.

Any chance of that?

~Allison

[Vasik Greif]

Hi Dan,
thanks, that did the trick for removing all the keywords. However, you
cannot drop database tables in live installation - as I said, I will
have hundreds of resources each month, and will need to remove the old
ones on a regular basis. Deleting a resource should really delete
everything, including keywords, previews, metadump.xml, and the whole
folder. Is there any reason why it doesn't work that way? And, most
importantly, any chance to get this fixed soon?

Thanks
Vasik

[Dan Huby]

On Nov 8, 2:43 pm, Vasik Greif <vaclav.gr...@gmail.com> wrote:
> Deleting a resource should really delete
> everything, including keywords, previews, metadump.xml, and the whole
> folder. Is there any reason why it doesn't work that way?

We'll look at it.

Dan

[Vasik Greif]

Great, thanks! IN the meantime, is there a way to remove keywords,
that don't return any results?

[Dan Huby]

On Nov 16, 12:33 pm, Vasik Greif <vaclav.gr...@gmail.com> wrote:
> Great, thanks! IN the meantime, is there a way to remove keywords,
> that don't return any results?

You could probably do something like

delete from keyword where ref not in (select keyword from
resource_keyword)

...but I think database_purge.php already does something like that.
Besides, unless you are really worried about disk space right down to
the KB, it's really not worth doing in my view.

Dan

[Vasik Greif]

Thanks, I'll try that. I don't worry about disc space. I need this
because it makes no sense that autocomplete suggests keywords, that
don't return any result - I hated this behavior when testing - when I
select a keyword from the suggestions, I expect there's a resource
with the keyword...

Vasik

[Vasik Greif]

Did anyone looked at this already? This is still stopping me from
running RS on live site, as I need the delete function to be 100%
reliable .

Thanks
Vasik

[Vasik Greif]

From looking into the resource_functions.php, it seems that the
delete_resource function doesn't deal with the keywords table at all.
I think in the delete_resource we need to:

1) check if other resource has the same keyword, if so, let it stay in
the keywords table, if not, delete it
2) delete the whole folder, where the resource is

I'm not able to do this, with my poor php knowledge, but I think this
is quite an easy task. Could someone do this?

Thanks
Vasik

[Vasik Greif]

Back here, as I need to solve this. For deleting a whole direcotory, here's a dirty way to do it. To resource_functions.php add new function (copied from php unlink manual)

function unlinkRecursive($dir, $deleteRootToo)
{
    if(!$dh = @opendir($dir))
    {
        return;
    }
    while (false !== ($obj = readdir($dh)))
    {
        if($obj == '.' || $obj == '..')
        {
            continue;
        }

        if (!@unlink($dir . '/' . $obj))
        {
            unlinkRecursive($dir.'/'.$obj, true);
        }
    }

    closedir($dh);
  
    if ($deleteRootToo)
    {
        @rmdir($dir);
    }
  
    return;
}

and right before hook("afterdeleteresource") I added

$path1=get_resource_path($ref,true,"",false,false);
$path2=str_replace("include/../",'',$path1);
$pos=strripos($path2,"/");
$path3=substr($path2,0,$pos);
unlinkRecursive($path3,true);

I'm sure there's an easy way to get the path, I just don't know how to do that. Going to look on the keyword delete too, but as you can see, I suck in php/mysql and won't be able to this correctly (though I suppose it will work somehow), so would be glad if anyone who knows php does that right.

Thanks
Vasik

[Vasik Greif]

And, once more here, for the keyword deletition, I came up with this

$keywords_to_delete = sql_query("select from resource_keyword where resource='$ref'");
$keyword_refs = mysql_fetch_array($keywords_to_delete)
foreach($keyword_refs as $keyword_ref)
        {
        $result = sql_query("select ref from keyword where ref='$keyword_ref'");
        if (!$result){
            sql_query("delete from keyword where ref='$keyword_ref'");
            }
        }

This is not tested yet. Anyone can have a look at this and say if the logic is good and it wont break things?

Thanks
Vasik

[Paul Manno]

Don't think that will work.  I think you should check to see if any other resource references that keyword before you delete it.

Paul

Thanks
Vasik

--

You received this message because you are subscribed to the Google Groups "ResourceSpace" group.

To view this discussion on the web visit https://groups.google.com/d/msg/resourcespace/-/AashHkjfpxAJ.

[Vasik Greif]

Aaaah, right, my check was wrong, I was checking in wrong table, heres another take, please let me know what do you think

$keywords_to_delete = sql_query("select from resource_keyword where resource='$ref'");
$keyword_refs = mysql_fetch_array($keywords_to_delete);
foreach($keyword_refs as $keyword_ref)
        {

        $result = sql_query("select resource from resource_keyword where keyword='$keyword_ref'");

[Paul Manno]

Step 1: Find all keywords attached to this resource ($ref), then of those keywords, find those that are referenced once (this is a fairly costly query and research should be done to optimize the indexing to support it).

$keywords = sql_query("select keyword, count(*) from resource_keyword where keyword in (select distinct keyword from resource_keyword where resource='$ref') group by keyword having count(*) =1");

Step 2:  Now we have an array of keywords that are only referenced once, so we can assume that this resource is the only resource touching these keywords, so we can delete them.

foreach($keywords as $keyword)
{
   sql_query("delete from keyword where ref=$keyword[0]");
}

Step 3: Delete the rows in resource_keyword for this resource.
   sql_query("delete from resource_keyword where resource='$ref'");

Step 4: Delete the resource, which I think there is a function for already (and it may do step 3 already also).

--

You received this message because you are subscribed to the Google Groups "ResourceSpace" group.

To view this discussion on the web visit https://groups.google.com/d/msg/resourcespace/-/5jBanUDp3DEJ.

[Vasik Greif]

Looks great, and is much better than what I did, thanks! Other
question is on the collection_keyword table, maybe we should take into
account that one too?

Thanks
Vasik

[Paul Manno]

Yes, you are right.  The collection_keyword table should be looked at too before deleting the keyword.

[Paul Manno]

I would recommend that Tom and/or Dan have a look at the queries also before you run them in a production environment.

Paul

[Vasik Greif]

Could you update the query to take collection_keyword into account
too? I think the logic should be if the keyword is referenced once
only in the resource_keyword table and is not referenced in the
collection_keyword table, than delete it, right?

Thanks
Vasik

[Paul Manno]

Updated step 1 to exclude any keywords in collection_keywords table

Step 1: Find all keywords attached to this resource ($ref), then of those keywords, find those that are referenced once (this is a fairly costly query and research should be done to optimize the indexing to support it).

$keywords = sql_query("select keyword, count(*) from resource_keyword where keyword in (select distinct keyword from resource_keyword where resource='$ref') and not exists (select 'x' from collection_keyword where resource_keyword.keyword = collection_keyword.keyword) group by keyword having count(*) =1");

Step 2:  Now we have an array of keywords that are only referenced once, so we can assume that this resource is the only resource touching these keywords, so we can delete them.

foreach($keywords as $keyword)
{
   sql_query("delete from keyword where ref=$keyword[0]");
}

Step 3: Delete the rows in resource_keyword for this resource.

   sql_query("delete from resource_keyword where resource='$ref' and not exists (select 'x' from collection_keyword where collection_keyword.keyword=resource_keyword.keyword)");

Step 4: Delete the resource, which I think there is a function for already (and it may do step 3 already also).

[Vasik Greif]

Thanks! Tom & Dan, could you have a look at this?

Vasik

[Vasik Greif]

Bumpity bump, trying it again, Tom/Dan, could you have a look at this
thread? Seems to me like quite important thing.

Thanks
Vasik

On 5 led, 00:05, Vasik Greif <vaclav.gr...@gmail.com> wrote:
> Thanks! Tom & Dan, could you have a look at this?
>
> Vasik
>
> On 4 led, 23:39, Paul Manno <pgma...@gmail.com> wrote:
>
>
>
>
>
>
>
> > Updated step 1 to exclude any keywords in collection_keywords table
>

> > Step 1: Findallkeywords attached to this resource ($ref), then of those

> > keywords, find those that are referenced once (this is a fairly costly
> > query and research should be done to optimize the indexing to support it).
>
> > $keywords = sql_query("select keyword, count(*) from resource_keyword where
> > keyword in (select distinct keyword from resource_keyword where
> > resource='$ref') and not exists (select 'x' from collection_keyword where
> > resource_keyword.keyword = collection_keyword.keyword) group by keyword
> > having count(*) =1");
>
> > Step 2:  Now we have an array of keywords that are only referenced once, so
> > we can assume that this resource is the only resource touching these
> > keywords, so we candeletethem.
>
> > foreach($keywords as $keyword)
> > {

> >    sql_query("deletefrom keyword where ref=$keyword[0]");
>
> > }
>
> > Step 3:Deletethe rows in resource_keyword for this resource.
> >    sql_query("deletefrom resource_keyword where resource='$ref' and not

> > exists (select 'x' from collection_keyword where
> > collection_keyword.keyword=resource_keyword.keyword)");
>

> > Step 4:Deletethe resource, which I think there is a function for already

> > (and it may do step 3 already also).
>
> > On Wed, Jan 4, 2012 at 4:22 PM, Vasik Greif <vaclav.gr...@gmail.com> wrote:
> > > Could you update the query to take collection_keyword into account
> > > too? I think the logic should be if the keyword is referenced once
> > > only in the resource_keyword table and is not referenced in the

> > > collection_keyword table, thandeleteit, right?

>
> > > Thanks
> > > Vasik
>
> > > On 4 led, 23:09, Paul Manno <pgma...@gmail.com> wrote:
> > > > Yes, you are right.  The collection_keyword table should be looked at too
> > > > before deleting the keyword.
>
> > > > On Wed, Jan 4, 2012 at 3:56 PM, Vasik Greif <vaclav.gr...@gmail.com>
> > > wrote:
> > > > > Looks great, and is much better than what I did, thanks! Other
> > > > > question is on the collection_keyword table, maybe we should take into
> > > > > account that one too?
>
> > > > > Thanks
> > > > > Vasik
>
> > > > > On 4 led, 22:42, Paul Manno <pgma...@gmail.com> wrote:

> > > > > > Step 1: Findallkeywords attached to this resource ($ref), then of

> > > those
> > > > > > keywords, find those that are referenced once (this is a fairly
> > > costly
> > > > > > query and research should be done to optimize the indexing to support
> > > > > it).
>
> > > > > > $keywords = sql_query("select keyword, count(*) from resource_keyword
> > > > > where
> > > > > > keyword in (select distinct keyword from resource_keyword where
> > > > > > resource='$ref') group by keyword having count(*) =1");
>
> > > > > > Step 2:  Now we have an array of keywords that are only referenced
> > > once,
> > > > > so
> > > > > > we can assume that this resource is the only resource touching these
> > > > > > keywords, so we candeletethem.
>
> > > > > > foreach($keywords as $keyword)
> > > > > > {

> > > > > >    sql_query("deletefrom keyword where ref=$keyword[0]");
>
> > > > > > }
>
> > > > > > Step 3:Deletethe rows in resource_keyword for this resource.
> > > > > >    sql_query("deletefrom resource_keyword where resource='$ref'");
>
> > > > > > Step 4:Deletethe resource, which I think there is a function for

> > > > > already
> > > > > > (and it may do step 3 already also).
>
> > > > > > On Wed, Jan 4, 2012 at 12:15 PM, Vasik Greif <vaclav.gr...@gmail.com
>
> > > > > wrote:
> > > > > > > Aaaah, right, my check was wrong, I was checking in wrong table,
> > > heres
> > > > > > > another take, please let me know what do you think
>
> > > > > > > $keywords_to_delete = sql_query("select from resource_keyword where
> > > > > > > resource='$ref'");
> > > > > > > $keyword_refs = mysql_fetch_array($keywords_to_delete);
> > > > > > > foreach($keyword_refs as $keyword_ref)
> > > > > > >         {
> > > > > > >         $result = sql_query("select resource from resource_keyword
> > > > > where
> > > > > > > keyword='$keyword_ref'");
>
> > > > > > >         if (!$result){

> > > > > > >             sql_query("deletefrom keyword where

[Tom Gleason]

I agree a full delete of the resource folder might be best, rather than what we're doing now which is trying to find all the files specifically (which does seem to result in orphaned files sometimes).
As for deleting unused keywords, I agree with Dan that database_prune.php should already do that.

Thanks for your ideas. I'll be looking at this; we just have to be extra careful when working on delete functions!

Tom Gleason
Vice President, Engineering
Colorhythm LLC

http://www.colorhythm.com

Main Office:  +1 415-399-9921
Fax: +1 253-399-9928

Mobile:  +1 347-537-8465

tgle...@colorhythm.com

[Tom Gleason]

Have you looked at the remove_keyword_mappings function and attempted to use that? That would probably be the right way to get started on a function to delete all keywords associated with a resource.

[Vasik Greif]

Hi Tom, thanks. Yes, anything with delete has to be dealt carefully
with. As you could see during this thread, I'm not the one who is able
to solve this correctly, I suck at php mysql. I just need this to be
solved somehow, as it stops me from running live install of
ResourceSpace.

The hack for deleting the whole folder works in my testing install,
and I'm pretty sure anyone who knows php better than I could improve
it easily so it will be generic and could be included in RS core.

For deleting the keywords, what does the remove_keyword_mapping
function do? What we did with Paul (well, what Paul did) is before
deleting the keyword, check, if it's used anywhere else, if not,
delete it. Is it the same what the function you mention does?

Thanks
Vasik

On 9 led, 23:30, Tom Gleason <t...@buildadam.com> wrote:
> Have you looked at the remove_keyword_mappings function and attempted to
> use that? That would probably be the right way to get started on a function
> to delete all keywords associated with a resource.
>
>
>
>
>
>
>
>
>
> On Mon, Jan 9, 2012 at 5:28 PM, Tom Gleason <t...@buildadam.com> wrote:
> > I agree a full delete of the resource folder might be best, rather than
> > what we're doing now which is trying to find all the files specifically
> > (which does seem to result in orphaned files sometimes).
> > As for deleting unused keywords, I agree with Dan that database_prune.php
> > should already do that.
>
> > Thanks for your ideas. I'll be looking at this; we just have to be extra
> > careful when working on delete functions!
>

> > Colorhythm LLChttp://www.colorhythm.com

>
> > Main Office:  +1 415-399-9921
> > Fax: +1 253-399-9928
> > Mobile:  +1 347-537-8465

> > tglea...@colorhythm.com

>
> --
>
> Tom Gleason
> Vice President, Engineering

> Colorhythm LLChttp://www.colorhythm.com

>
> Main Office:  +1 415-399-9921
> Fax: +1 253-399-9928
> Mobile:  +1 347-537-8465

> tglea...@colorhythm.com

[Vasik Greif]

Back here once more. I checked the database_prune.php and you're
right, it does what Paul suggested - so Paul's should be safe. So,
isn't better approach to run it at the time the resource is deleted,
without having to run database_prune? Will it slow down the delete a
lot? Why does database_prune actually exist? There should be no need
for it..

Thanks
Vasik

On Jan 9, 11:41 pm, Vasik Greif <vaclav.gr...@gmail.com> wrote:
> Hi Tom, thanks. Yes, anything withdeletehas to be dealt carefully

> with. As you could see during this thread, I'm not the one who is able
> to solve this correctly, I suck at php mysql. I just need this to be
> solved somehow, as it stops me from running live install of
> ResourceSpace.
>
> The hack for deleting the whole folder works in my testing install,
> and I'm pretty sure anyone who knows php better than I could improve
> it easily so it will be generic and could be included in RS core.
>
> For deleting the keywords, what does the remove_keyword_mapping
> function do? What we did with Paul (well, what Paul did) is before

> deleting the keyword, check, if it's used anywhere else, if not,deleteit. Is it the same what the function you mention does?

>
> Thanks
> Vasik
>
> On 9 led, 23:30, Tom Gleason <t...@buildadam.com> wrote:
>
>
>
>
>
>
>
> > Have you looked at the remove_keyword_mappings function and attempted to
> > use that? That would probably be the right way to get started on a function

> > todeleteall keywords associated with a resource.

>
> > On Mon, Jan 9, 2012 at 5:28 PM, Tom Gleason <t...@buildadam.com> wrote:

> > > I agree a fulldeleteof the resource folder might be best, rather than

[David]

An update on this thread: I just committed changes that do the following:

1. Remove the metadump.xml file when a resource is deleted, and then attempt to remove the filestore folder. This will only work if it is empty -- if there is still something in it, then it will fail silently. If anyone has this happen, I'd like to know what isn't getting deleted so that we can troubleshoot it.

2. Added a prune_filestore.php utility to the tools folder that will remove any filestore folders that don't have a corresponding database record. This should allow administrators to clean up existing installations and remove all orphaned folders from the filestore.

As with anything involving deletion, be careful. I have run this on my production system and it worked as planned, but I make no warranties and suggest caution before using it. I included a "dryrun mode" with the pruning script so that you can see what it will do before using it: just call it with /pages/tools/prune_filestore.php?dryrun=true to use it in this mode. It will show a list of orphaned directories and files that it would be deleting so that you can look over it and make sure it's not going to run amok.

-David

On Wednesday, November 2, 2011 8:50:54 AM UTC-4, Vasik Greif wrote:

Hi, how can I delete all resources and keywords I added during my
testing? I followed this thread:
http://groups.google.com/group/resourcespace/browse_thread/thread/fdf7749a042707ad
, I runned pages/tools/database_prune.php, when I do clear search as
admin, I don't get any results, but I still have some resources left
in filestore, some keywords left in db and all the metadump.xml files
are still in filestore.

Any ideas?
Thanks

Vasik