Bulk delete for authority records

[yooyoungl...@gmail.com]

Hi,

My institution would like to delete authority records. On the documentation page, there is information about how to delete an authority record manually, but no bulk delete authority records. I found this post in this forum, but it was in 2014, so I would like to double check with you if there is anyway to bulk delete even in the backend. The criteria is to be: if the authority record doesn't have any content other than the name field, we would like to delete them all. Can I delete them in the backed database? Are there actor and actor_i18n tables?

Your help is really appreciated!!

Thanks,

Yoo Young Lee

University of Ottawa Library

[sbr...@artefactual.com]

Hello!

There is not currently a way to delete authority records in bulk from the WebUI or the CLI tasks.  It is possible to delete authority records using a script launched using the CLI "tools:run" task.

I have created a script that uses the Propel ORM to perform a query on the Actors tables.  It will return all Actors where authorized_form_of_name AND dates_of_exisitence AND history are all NULL (blank in the AtoM interface) and delete them.

You will note in the script that I have not fully checked if ALL the fields are blank - you will need to edit the script to add any additional criteria using the lines I’ve already added as an example.

WARNING:

• This process is untested.

• You should make a backup of your AtoM database and know how to restore your system should you need.

• Ideally this should be conducted on a test system first if you have one available.

• Proceed at your own risk!

The script contains additional instructions in the comment block of the script itself.  The script can be found here:  

• https://gist.github.com/sbreker/373f0aa7a92e23d392d8c03b53d7a34d
  

The list of actor_i18n fields names that can be checked are listed here for actor_i18n:

• https://github.com/artefactual/atom/blob/qa/2.5.x/lib/model/om/BaseActorI18n.php#L10-L24
  

….and here for actor:

• https://github.com/artefactual/atom/blob/qa/2.5.x/lib/model/om/BaseActor.php#L10-L20
  

Additionally, here is the reference for the Propel Criteria object:

• http://api.propelorm.org/1.6.0/Criteria.html
  

Hope this helps!  Let me know if you have any questions.

Steve

Steven Breker, BSc
AtoM Programmer
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

[Dan Gillean]

Just to correct one typo in Steve's explanation of his script: 

Currently the script is looking for authority records that DO have an authorized form of name, but where dates of existence and history are NULL (i.e. blank), as per your request. 

You can see this in line 44: 

• https://gist.github.com/sbreker/373f0aa7a92e23d392d8c03b53d7a34d#file-deleteauth-php-L44
  

Right now the criteria for that line is ISNOTNULL. If you changed this to ISNULL, then the script would look for authority records where all 3 current criteria (i.e. authorized form of name, dates of existence, and history) are all blank / null. 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To post to this group, send email to ica-ato...@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/5dea6cbe-8869-4ca3-8549-af1bf2765283%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Yoo Young Lee]

Hi Steve and Dan,

Thank you so much for your help! I will look into your suggestion and if I have any questions, I will let you know.

Yoo Young

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAC1FhZKNmMd%2BsFN-JY0oCMC7%3DB24XXc4nDi4C4MzwFc410aNFw%40mail.gmail.com.

[yooyoungl...@gmail.com]

Hi Steve and Dan,

As our team discussed further, what we wanted to delete were actually any authority records which were not associated with any archival descriptions. 

For example, if you see Canadian Women's Studies Association (CWSA), although there is only authorized form of name, it is a subject of one archival descriptions and we still want to keep them.

The records we wanted to delete were like Catherine Collett. 

I couldn't find any information in the Actor or Actor_i18n... 

Could you guide me where to find this information?

Thank you,

Yoo Young

To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/5dea6cbe-8869-4ca3-8549-af1bf2765283%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

[sbr...@artefactual.com]

Hello Yoo Young

I have created a new script that is a start to one that might be used as you describe.  

I should emphasize again:

• This process is untested.

• You should make a backup of your AtoM database and know how to restore your system should you need.

• Ideally this should be conducted on a test system first if you have one available.

• Proceed at your own risk!

The script can be found here:  https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5

 It will delete any authority that is unreferenced.

Unreferenced is defined as:

• it's not a name access point for any descriptions
  
• In this case actors and descriptions are linked via the relation table since the relationship is "many to many"
• it's not linked via the events table - e.g. not a creator
  
• In this case actors and descriptions are linked via the event table since the relationship is "many to many"

If you look at the script, you can see that I have commented out the 'delete' line so that it will not make any changes to the database.  This way you can try it out on a demo system and see what it outputs to the screen.  

It also has a verbose option which is set to True, so it will output those "KEEP" message lines as well.

Hopefully this is a good start for you!  Let us know if you have any questions! 

Steve  

To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To post to this group, send email to ica-ato...@googlegroups.com.

Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/5dea6cbe-8869-4ca3-8549-af1bf2765283%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To post to this group, send email to ica-ato...@googlegroups.com.

[Yoo Young Lee]

Hi Steve,

Just quickly ran a test in QA and it worked really well! Thank you so much and have a great weekend!!

Yoo Young

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/b79bdc2a-bb33-4be4-8de2-bf5284c182e1%40googlegroups.com.

[yooyoungl...@gmail.com]

Hi Steve,

Could I bother you one more time?

Your delete_unreferenced_actors.php worked really well in the test environment, but the issue was that it deleted only ~300 authority records out of ~5600. I wanted to add a new criterion which was to keep authority records when they are linked via the events table (so only if they're a creator), but delete other records although they are a name access point . I've tried to understand your script and modify them to meet the new criterion, but I couldn't as I didn't fully understand the database structure in AtoM.

Thank you so much for your help!

Yoo Young

To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

[sbr...@artefactual.com]

Hello!  

I wonder why the records you were expecting to be deleted were not?  Did the record you sent as an example get deleted?

e.g.: https://biblio.uottawa.ca/atom/index.php/collett-catherine

Regarding deleting authority records that are linked as name access points

This record below which you sent as an example of a record you would like to keep is linked to a description as a name access point:

https://biblio.uottawa.ca/atom/index.php/candian-womens-studies-association-cwsa

If I were to modify the script so that authorities that are name access points are deleted in addition to unreferenced authorities, then this record would have been deleted.  Is this ok?

Steve

[yooyoungl...@gmail.com]

Hi Steve,

Sorry for confusion and thank you so much for your help!

Yes, we would like to delete any authority records except those which are linked via the events table (creator).

Just a bit of background history, there were lots of authority records created during csv import. At that time, we didn't really think of authority records and now the team would like to improve and utilize AtoM more better way.

In order to do that, we would like to first delete unnecessary authority records. Initially, we would like to delete any authority records which don't have any content other than the name field (my first email), but the team discovered that most of them are in this category, so they would like to add another criterion which was neither name access point nor creator (my second email), but still most of the authority records are related to "subject of" so this didn't give us any differences. So the team finally decided to delete them except the records with creator.

Thank you so much!

Yoo Young

[Steve Breker]

Ok.  I wanted to make sure you'd considered what the effects will be on the examples you sent.

So to modify the script to only keep authority records that are linked via the events table you just need to remove the reference to getActorRelations() which was retrieving actors that are name access points:

$relations = getActorRelations($actor->id);

...and any additional references to "$relations".  This way it will only check for information objects linked via the events table.

So you would remove these lines:

https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L29

https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L36

https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L45

https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L49-L60

I would also recommend testing with the delete line commented out so that it does not affect anything:

//$actor->delete();

https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L37

Make sure to test first in a test or demo AtoM instance and have backups before running.

Hope that helps!

Steve

You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/voM3OYZpghM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.

To post to this group, send email to ica-ato...@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/92ba4477-5a93-4d2d-8443-5b60635d216d%40googlegroups.com.

[Yoo Young Lee]

Hi Steve,

I've commented these lines and run a script, but I got an error message saying that unable to execute DELETE statement as a foreign key constraint fails... (although commented the delete line worked though). But maybe I already ran the script and I didn't really repopulate search terms or start services. Just tried to run a script on top of that.

Tomorrow, I will just dump again data from Prod to Dev and run the script and let you know how it goes!

Thank you so much for your help!!

Yoo Young

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CA%2BhFp559CG2CHZsFZ2cC3jtMGaJQD42Mc4GBP3j9Smr2%3DFb7ZA%40mail.gmail.com.

[Steve Breker]

That's strange - I did not get that result in my test system.  

You may have noticed but you will also have to modify the 'if' statements to not check $resource:

https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L31

would become : if (count($eventRelations) == 0)

https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L40

would become: if ((count($eventRelations) > 0) && $verbose)

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAM7%2B6mQUyxB4r_Q48_YW4mfP_b9GaKcsKyFim7fc1tP3ojH6Cg%40mail.gmail.com.

[Yoo Young Lee]

Hi Steve,

I still got the same error message:

"Unable to execute DELETE statement. [wrapped: SQLSTATE[23000]: Integrity constraint violation: 1451 cannot delete or update a parent row: a foreign key constraint fails ('atom'.'relation', CONSTRAINT 'relation_FK_3' FOREIGN KEY ('object_id') REFERENCES object' ('id'))].

When I commented the delete statement, it seems working, but with the line 37 (https://gist.github.com/sbreker/54df1500d93a52de04a4513e9a5cdde5#file-delete_unreferenced_actors-php-L37), I got the error message... That's why I couldn't figure it out at beginning...

Thank you so much!!

Yoo Young

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CA%2BhFp56f6qT09KVRyOp-JwscShT8kuBUcaftNkSfCP1_3H0Q7Q%40mail.gmail.com.

[Yoo Young Lee]

Hi Steve,

The AtoM version is 2.3.0-138 if this is helpful...

Thanks,

Yoo Young

[Steve Breker]

Ok - the Authority model's delete() function has changed between 2.3 and 2.4/2.5.  A couple of lines need to be added to the script to delete the relations first.  

I have updated the gist here:

https://gist.github.com/sbreker/8f8a5c75b65e69b18eb0d44794585308

You will need to uncomment the two ->delete() lines to cause the database to actually be updated:

https://gist.github.com/sbreker/8f8a5c75b65e69b18eb0d44794585308#file-delete_non_event_linked_actors-php-L37

https://gist.github.com/sbreker/8f8a5c75b65e69b18eb0d44794585308#file-delete_non_event_linked_actors-php-L40

Again, this is untested.  Please make backups and try on a test system first!

Steve

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAM7%2B6mSSrcU7w74HOCKr-Q0hZyx8HMRoWQRhBAA5FAYnL9MDbQ%40mail.gmail.com.

[Yoo Young Lee]

Hi Steve,

It's working well! Thank you so much for all your help!

Yoo Young

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CA%2BhFp544heTHroeUceUQ0A5e0vW50LmReg8BmE1amiXt%3DOj7Eg%40mail.gmail.com.