Duplicate Archival Institutions cleanup

[PHXHoward]

Good morning.  We have been users of AtoM for more than 14 years. Our archivist and users love it. We are a small non profit organization and have five or six people doing occasional data entry.  Over the years we have experienced a problem where the data entry people have created multiple identical Archival Institutions because they typed or pasted in the name rather than selecting an existing from the drop down list.

I'm wondering if there is a way to quickly update the archival descriptions to put everything under one institution and delete all the duplicates institutions? We have multiple listings for duplicate institutions with just a few records in some of them.

I'm a database administrator by professions so I am very comfortable querying the backend MySQL database if an UPDATE query would fix it easily.

Thanks for reading.  Best wishes.

Howard 

[pieters...@gmail.com]

Hi Howard,

 

Yes, this is fixable with SQL, and the good news is that the reassignment really is a single UPDATE. But the order matters, and there is one trap that will bite you if you delete first.

 

As always make a backup of the database. I tested this but there is no guarantee from my side.

 

The trap. In AtoM's schema, exactly one column points at an archival institution: information_object.repository_id. It is declared ON DELETE SET NULL. So if you delete a duplicate institution before reassigning its descriptions, MySQL will not stop you and will not error. It will quietly set those descriptions' repository_id to NULL, and you have turned a duplicate-institution problem into an orphaned-description problem. Reassign first, delete second.

 

Finding the duplicates. Institutions are actors, so the name lives in actor_i18n:

        

  SELECT TRIM(ai.authorized_form_of_name) AS name,

         COUNT(*)                         AS copies,

         GROUP_CONCAT(r.id ORDER BY r.id) AS repository_ids

  FROM repository r

  JOIN actor_i18n ai ON ai.id = r.id AND ai.culture = 'en'

  GROUP BY TRIM(ai.authorized_form_of_name)

  HAVING COUNT(*) > 1;

 

Then get the holdings count per candidate so you can pick the keeper deliberately:

        

  SELECT r.id, ai.authorized_form_of_name, s.slug,

         (SELECT COUNT(*) FROM information_object io WHERE io.repository_id = r.id) AS descriptions

  FROM repository r

  JOIN actor_i18n ai ON ai.id = r.id AND ai.culture = 'en'

  LEFT JOIN slug s ON s.object_id = r.id

  ORDER BY ai.authorized_form_of_name;

 

Pick the keeper on the strength of its ISDIAH content, not its record count. The duplicate with three descriptions may be the one where somebody filled in the contact details and opening hours. Check before you choose, because none of that gets merged for you.

 

The reassignment.

    

  UPDATE information_object

     SET repository_id = <keeper_id>

   WHERE repository_id IN (<dupe_id>, <dupe_id>, ...);

 

One thing to be aware of here: in AtoM only the descriptions that explicitly carry a repository_id need touching. Lower levels inherit from their ancestors at display time (QubitInformationObject::getRepository() walks up the tree when the column is null). Normally that means only your top-level descriptions hold the value, but since this came from hand-entry, it is worth checking whether anybody set it partway down a hierarchy too. The query above will show you.

 

Deleting the empties, and why I would not do this bit in SQL. A raw DELETE FROM object WHERE id = ... does cascade correctly at the database level, through actor and repository and on to slug, notes, other names, contact information and relations. The problem is everything that lives outside MySQL. AtoM's own QubitRepository::delete() also clears the cached institution list (search:list-of-repositories:*), re-indexes every affected description, and removes the Elasticsearch document. Delete in SQL and  you keep ghost institutions in the search facets and the advanced-search dropdown until something forces a rebuild.

 

For six or so duplicates it is genuinely faster to do the UPDATE in SQL, then delete the now-empty institutions through Manage > Archival institutions in the interface. Let the application do the part it is careful about.

 

Afterwards, regardless of route, the search index still holds the old values from your UPDATE:

 

php symfony search:populate

php symfony cc

 

Run those as your web user rather than root, or you will leave root-owned files in cache/ that the web process t Jump to bottom (ctrl+End) ↓

 

Groete / Regards

Johan Pieterse

082 337-1406

--
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 view this discussion visit https://groups.google.com/d/msgid/ica-atom-users/4cf60d4a-740a-45bc-b268-707cd7ae2778n%40googlegroups.com.

[PHXHoward]

Thank you Johan. I understand what you are saying. Some database structure looks like spaghetti strings with intermediary tables and such.  I'm glad to see that I can UPDATE the repository_id in the information_object table and then once the duplicate repositories have no associated records, those repositories can be cleaned up.

I'll take a database backup first and then compare the number of archival records before and after the UPDATE to make sure none had a cascading delete. 

Thanks much,

Howard