RE: [atom-users] Obtaining list of all repository slugs

[Guedj Romain]

Hi Carolyn,

 

If you have only one database for all your repositories, you can make this query in the database for one object here you need the object_id

 

SELECT * FROM atom.slug where object_id = '5961744' ;

 

If you want all slugs

SELECT * FROM atom.slug  ;

 

Meilleures salutations

 

Romain Guedj, e-Archiviste

Romain...@fr.ch, T +41 26 305 13 74

—

Bibliothèque cantonale et universitaire BCU

Kantons- und Universitätsbibliothek KUB

Secteur technologies du web et infrastructure informatique

Abteilung Webtechnologien und Informatik-Infrastruktur

Rue de la Carrière 22, Case postale, 1701 Fribourg

T +41 26 305 13 33, www.fr.ch/bcuf

—

Direction de la formation et des affaires culturelles DFAC
Direktion für Bildung und kulturelle Angelegenheiten BKAD

—

ETAT DE FRIBOURG

STAAT FREIBURG

 

De : ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> De la part de Carolyn Sullivan
Envoyé : jeudi 7 septembre 2023 01:29
À : AtoM Users <ica-ato...@googlegroups.com>
Objet : [atom-users] Obtaining list of all repository slugs

 

Hello,

 

I was wondering if there was a way to get a list of the slug for all the repositories in AtoM in case we'd like to run a task to publish the drafts for multiple repositories from the commandline.  I took a look at this: https://wiki.accesstomemory.org/images/d/d4/Atom-erd-2023-07-qa27.pdf

 

but I can't seem to figure out how repositories connect to slugs in the ERD.  Suggestions?

 

Thanks,

Carolyn.

--
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/fcbecff3-c22f-435b-989b-53838f010ba1n%40googlegroups.com.

[Dan Gillean]

Hi Carolyn and Romain, 

Romain, thanks for the suggestion! For cases where you have multiple repositories, I am certainly no SQL expert but I think I have managed to figure this out! But first: even though the queries below are just SELECTs and shouldn't alter any data, we ALWAYS strongly recommend making a backup before accessing the MySQL command prompt, especially for a production instance! See: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#backing-up-the-database
  

So: every main entity in AtoM has an entry in the object table - this is where the base unique database IDs (the object IDs) are assigned, that are used as keys in pretty much every other entity table. if we take a look at the object table: 

• describe object;

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| class_name    | varchar(255) | YES  |     | NULL    |                |
| created_at    | datetime     | NO   |     | NULL    |                |
| updated_at    | datetime     | NO   |     | NULL    |                |
| id            | int          | NO   | PRI | NULL    | auto_increment |
| serial_number | int          | NO   |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+

We can also see that each row in the object table has a class assigned to it - and one of these classes is QubitRepository. Therefore, we can JOIN our slug table to the object table in our query on the ID fields, and use the class_name to limit what we return to the repository records: 

• SELECT s.slug FROM slug s JOIN object o ON s.object_id = o.id WHERE o.class_name = 'QubitRepository';
  

Now, we're not quite done - there's one more thing to point out here. Below is what I got returned from my test instance with this query: 

+-----------------------------------------+
| slug                                    |
+-----------------------------------------+
| 39bn-wf28-hes3                          |
| art-gallery-of-ontario                  |
| burlington-historical-society           |
| wilfrid-laurier-university-archives     |
| womens-college-hospital                 |
| city-of-greater-sudbury-archives        |
| university-of-british-columbia-archives |
| yukon-archives                          |
| artefactual-archives                    |
| service-darchives-test                  |
| city-of-winnipeg-archives               |
| united-nations-archives                 |
+-----------------------------------------+
12 rows in set (0.02 sec)

Looks pretty good... but there is actually one more entry here than the 11 repository records I actually have in my installation. 

I recall one of the developers telling me long ago that all main entities in AtoM have a hidden root entity object to which all the others are sort of attached as descendants, and that these hidden entities tend to have fixed static IDs assigned - for example, for information objects, it's 1 - you can see some examples of root IDs being used in our example queries in the "Delete slugs from AtoM" query section in our docs. 

I didn't know what the root ID for repositories was, but I took that first generated slug (the outlier in the results above) and used our "find object ID from a slug" query from the docs with it, and got 6 back. I am not 100% that this IS in fact the root repository object, but it sure seems like it! If that is the case, then we can run the same query but filter out that result based on ID, like so: 

• SELECT s.slug FROM slug s JOIN object o ON s.object_id = o.id WHERE o.class_name = 'QubitRepository' AND s.object_id <> 6;
  

Now I get 11 results as expected. You may want to repeat the same investigation in your instance to confirm that the root repository ID is consistently 6 - but in any case, most object_id values for records created by end users tend to be 4-6 digits anyway, so even if this isn't globally correct, it shouldn't filter out an actual repository record. 

Hope this helps!

----------------------------------------------------------------------------------------------

Meanwhile.... 

I actually read your question wrong at first, and thought that you were looking for the slugs of descriptions associated with a specific repository. I didn't fully solve this, but did make some good progress - so I figured I would also share this, in case it helps you or someone else in the future with a similar question. 

It is only partially solved however: I can figure out how to return records that are manually linked to the repository (i.e. typically top-level records), but because AtoM uses inheritance to minimize the number of direct relations between information objects and repository records, things get more complicated for ALL records associated with a repository, unfortunately...

Anyway, I will share what I've got so far. For the top-level / directly linked descriptions: 

There is a repository_id field in the information_object table. If we join that table with the slug table, then we can make a SELECT of the fields we want, using the object_id of the related repository to limit the results to those descriptions with a matching repository_id. 

So, first, let's get the object_id of our repository record. You can use the slug to get this: 

• SELECT object_id FROM slug WHERE slug='your-slug-here';
  
• See: https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#finding-the-object-id-of-a-record

Now with that done, let's list our slugs - we will also include the object_id of each description, and the identifier. If we wanted a string-based field (i.e. one that can be translated) like the description title, then we would also have to join the information_object_i18n table, so for simplicity I have avoided that. The identifier is not translatable, so it's in the base information_object table. Using the number we got above, let's now put together our query to select the slug from the slug table, as well as the object_id and identifier from the information objects: 

• SELECT * FROM slug s JOIN information_object io ON s.object_id = io.id WHERE io.repository_id =''; 
  

I used my test "Artefactual Archives" repository with 2 top-level test descriptions linked to it.  Entering the Artefactual Archives' repository_id value from the first query gave me an output like so: 

+--------------------------------+-----------+------------+
| slug                           | object_id | identifier |
+--------------------------------+-----------+------------+
| atom-markdown-tests            |   2004235 | MARK       |
| digital-object-test-collection |   2028959 | DIGITEST   |
+--------------------------------+-----------+------------+

If you wanted to select different fields in the output, you can use the describe query to see the fields available on a table, i.e.:

• describe SLUG;
• describe information_object; 

Cheers, 

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

@accesstomemory

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/ZR2P278MB103350A059B5EBB22C724C7B8AEEA%40ZR2P278MB1033.CHEP278.PROD.OUTLOOK.COM.

[Lrellis D'erth]

Thank you all for your help!  Much appreciated!

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAC1FhZKcC_E-99NkK7MLXK6y_F9L7QPC%3DWxwz1eVWUREtWVvqQ%40mail.gmail.com.