Bulk deletion of archival descriptions

[jesus san miguel]

Hi all,

Is there any way to perform (command line of course) a bulk deletion of archival descriptions based on a certain slug pattern? For example, to delete all achival descriptions whose slug contains "-2"

Best,

Jesus

[Dan Gillean]

Hi Jesus, 

There's no existing AtoM command-line task that can do exactly this, but I suspect you could craft one using SQL. At 6pm my time on a Friday, I don't currently have time to try and craft an expression for you - I'm not a developer so it takes me a long time to work out! But hopefully I can point you in the right direction. 

First, some general resources. 

Basic information on how to access the MySQL command prompt can be found here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#accessing-the-mysql-command-prompt

You can see entity relation diagrams from AtoM's MySQL database on our wiki here, to better understand how it's organized: 

• https://wiki.accesstomemory.org/Development/ERDs#January_2020
  

The AtoM documentation page on Common database queries will not give you exactly what you want, but will provide a number of examples that have been tested in AtoM, that should familiarize you with the basic structure of a SQL query against AtoM's database. See: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/
  

You can easily see what columns are available in a table using DESCRIBE - for example: 

• DESCRIBE slug;
• DESCRIBE object;

Most importantly - if you are going to try modifying AtoM data via SQL, we STRONGLY recommend you make a backup of your data first! Proceed at your own risk, and take any advice I provide with a grain of salt - I'm not a developer or a database administrator; I'm trained as an archivist. I might be wrong about some of the following! Artefactual takes no responsibility for any issues resulting from the advice provided below. 

With that warning out of the way, next some implementation advice: 

If you want to perform deletions, you should do so from the object table - this is a base table in AtoM which all main entity types are linked to via ID, so deletions from here will cascade to any other relevant tables. This saves you from needing to know and explicitly delete from any table that might hold part of the data used in a description (and there are more than you think!).

I recently shared an example query showing how you could delete all the children of a particular record from the object table in this thread: 

• https://groups.google.com/g/ica-atom-users/c/d814e7LUD4o/m/qKFTric9DgAJ
  

You will have to do a JOIN with the slug table to be able to add the necessary parameters - that's the part that might take some experimentation, but the slug table should have the same object ID on it as the ID in the object table, so hopefully that's what you can perform the join on. This would be a good time to use the DESCRIBE command to see what the columns are named in the slug table, for example. To be honest, I don't actually recall if the object table and the slug table are directly linked - if not, things get more complicated, as you'll likely also need to join the information_object table to act as a bridge. Multiple joins are certainly beyond my level of knowledge, so let's hope that's not the case!

SQL supports using LIKE as a clause, so I think you could add deletion criteria where you say the slug value should be LIKE '%-2'; i.e. ends with -2. 

I'm CERTAIN THIS WILL NOT BE EXACTLY CORRECT, SO PLEASE DON'T TRY IT DIRECTLY, but altogether your query will probably look something like this (if multiple joins are not needed): 

• DELETE o FROM object o JOIN slug s ON o.id = s.id WHERE s.slug LIKE '%-2';

Remember to make a back up first!

Once you've done the deletions, you'll want to: 

• Restart PHP-FPM and if you're using it, Memcached
• Clear the application cache
• Rebuild the nested set
• Re-populate the search index

If you try this, please let me know how it goes - and query you ended up using! 

Cheers, 

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

@accesstomemory

he / him

--
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 on the web visit https://groups.google.com/d/msgid/ica-atom-users/65653309-da43-4836-8efa-5b6643523b0en%40googlegroups.com.