How to select slugs for all archival institutions from mysql?

30 views
Skip to first unread message

Carolyn Sullivan

unread,
Mar 16, 2023, 4:12:21 PMMar 16
to AtoM Users
Hello,

I'm trying to write a script that will publish all the archival drafts for me.  I tried doing it by archival institution, but for some reason, when I make my sql call to the object table, the QubitRepository items returned include multiple authority records... not just archival institutions.

#!/bin/bash
mysql -D atom_wm -e 'SELECT slug FROM object INNER JOIN slug ON object.id=slug.id WHERE object.class_name="QubitRepository";'|while IFS= read -r loop
do
        php symfony tools:update-publication-status published --repo $loop <<< "y"
done

Is there a problem in my database, given that I'm getting authority records returned from this query, or am I doing something wrong with this bash script?

Thanks,
Carolyn.

Dan Gillean

unread,
Mar 17, 2023, 8:14:05 AMMar 17
to ica-ato...@googlegroups.com
Hi Carolyn, 

A couple of things: 

First, it's helpful to know that in the database schema, a repository is actually a subtype of actor - so there is a link to the tables used for authorities, which is likely causing the behavior you're seeing. 

This question has come up before - fortunately for you! Which means I have previously tracked down an AtoM developer and had them help me craft something for this particular use case. Turns out it's more complicated than you'd expect! If you check the docs, you will see a query example for updating the publication status of  all records in a system, or at the end of the following section, there's a longer SQL query example showing how to update just those associated with a particular repository:
So: if you're trying to update all records, then there's no need for a script to find the IDs and pass them through the pub status task - you can just use SQL to do it for all records at once - in fact, the docs for the publication status task link to this SQL query and recommend using it if you're trying to update thousands of records at once! And if you only want to update one or a couple repositories, then use the longer SQL query example provided. 

Of course, as always when using scripts and SQL to modify your records, we strongly recommend you make a backup first! 

Hope that helps, 

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/4cb0445a-f759-480b-80b2-096921ef54b3n%40googlegroups.com.

Carolyn Sullivan

unread,
Mar 17, 2023, 2:51:29 PMMar 17
to AtoM Users
:O  

(1) I can't wait to test this.
(2) Thank you so much Dan for all your support, you're delightful!

Reply all
Reply to author
Forward
0 new messages