How to move a lot of series to a fond

65 views
Skip to first unread message

Nabil el filali

unread,
Apr 25, 2022, 4:35:28 PM4/25/22
to AtoM Users
Hello 
Is there any way to move 1500 series automatically to a fond via sql quey ? 



Thank you 

Dan Gillean

unread,
Apr 26, 2022, 12:40:35 PM4/26/22
to ICA-AtoM Users
Hi Nabil, 

Without more information, it's hard to say, but I will try to provide some general guidance so you can explore and refine this further yourself, depending on your needs. First, take a look at the Common AtoM Database Queries page in our documentation - it's a useful reference, and several of the queries I will provide below come directly from those examples. This page also includes information on how to access the MySQL command prompt. See:  
We also have database Entity Relationship Diagrams on the wiki, to help you understand how AtoM's database is organized: 
Also: please be warned that I have not tested the following query. Proceed at your own risk, and we strongly urge you to make a database backup before performing any SQL updates! See: 
Now, to dig into your question a bit: we'll be making changes in the information_object table, which is the primary database table for archival descriptions. Here's what it looks like: 

mysql> describe information_object;
+-------------------------+---------------+------+-----+---------+----------------+
| Field                   | Type          | Null | Key | Default | Extra          |
+-------------------------+---------------+------+-----+---------+----------------+
| id                      | int           | NO   | PRI | NULL    |                |
| identifier              | varchar(1024) | YES  |     | NULL    |                |
| oai_local_identifier    | int           | NO   | UNI | NULL    | auto_increment |
| level_of_description_id | int           | YES  | MUL | NULL    |                |
| collection_type_id      | int           | YES  | MUL | NULL    |                |
| repository_id           | int           | YES  | MUL | NULL    |                |
| parent_id               | int           | YES  | MUL | NULL    |                |
| description_status_id   | int           | YES  | MUL | NULL    |                |
| description_detail_id   | int           | YES  | MUL | NULL    |                |
| description_identifier  | varchar(1024) | YES  |     | NULL    |                |
| source_standard         | varchar(1024) | YES  |     | NULL    |                |
| display_standard_id     | int           | YES  | MUL | NULL    |                |
| lft                     | int           | YES  | MUL | NULL    |                |
| rgt                     | int           | YES  | MUL | NULL    |                |
| source_culture          | varchar(16)   | NO   |     | NULL    |                |
+-------------------------+---------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)


The field we want to update in this table is the parent_id. This field expects the object ID value of the parent record. Note that a record that has 1 as its object ID is a top-level record - in the example I provide below, I will be assuming that the Series you want to move are currently top-level records (i.e. have no parents above them), so we'll use that as part of our selection criteria. 

We'll also need to know the object ID of the target parent fonds. We can use SQL to find this, using the slug of the fonds as an input:
  • SELECT object_id FROM slug WHERE slug='your-slug-here';
Replace your-slug-here in the example above with the slug of the target fonds. See:
Now we can use that ID as an input for updating the series records.

The hardest part still remains, however, and will likely be up to you to determine: finding a SELECT query that will target only the series that you want to move. If it's every Series-level record in your AtoM instance, that makes things easier. For now, I'm going to assume in the following example that it's every Series that is a top-level record. 

We know we want to target descriptions that are Series - so we can use the level_of_description_id as one of our parameters. First we need to know the object ID of the Series term in our Levels of description taxonomy.

Fortunately, we have this as an example query in our documentation. The Levels of Description taxonomy has an ID of 34, and knowing that, we can output the names and IDs of each level of description term:

mysql> SELECT term.id, term_i18n.name FROM term LEFT JOIN term_i18n ON (term.id = term_i18n.id) WHERE term.taxonomy_id=34 AND term_i18n.culture='en';
+---------+--------------+
| id      | name         |
+---------+--------------+
|     198 | Item         |
|   69884 | Record group |
| 2000004 | Part         |
| 2003106 | Fonds        |
| 2003107 | Sous-fonds   |
| 2003108 | Collection   |
| 2004222 | Series       |
| 2004223 | Subseries    |
| 2004227 | File         |
| 2004244 | Sub-fonds    |
| 2004254 | Sub-series   |
| 2004306 | Volume       |
| 2004341 | Document     |
| 2020229 | folder       |
+---------+--------------+
14 rows in set (0.00 sec)


See:
IMPORTANT: your IDs may not be the same! Be sure to run the query above yourself and use the ID output for the Series level. I will use ID 2004222 in my example for Series, based on the output I got above - but my test instance will have different term IDs than your installation, so use the query above to find your Series term ID, and use that in the final query. 

Any series-level record that is currently a top-level description (i.e. it has no parents) should currently have a parent_id value of 1 - we want to update this value to be the ID of the target fonds.

So now, the following example *should* update all Series level descriptions that have no parents, to be parents of your target fonds. Replace XXXX in the example below with the ID of the target fonds (and make sure you are using the Series ID you found in place of the 2004222 example I a using below):

UPDATE information_object io
SET io.parent_id=XXXX
WHERE io.level_of_description_id=2004222
AND io.parent_id=1;


In a multi-repository instance, we could add another AND parameter to our select, using the repository_id of the target archival institution the same way we did for the level of description ID. That is, find the object ID of the related repository using the slug, and then use that as the ID when adding repository_id= as an additional select parameter in the WHERE part of the query. This would allow you to target only Series-level records that a) are top-level descriptions (i.e. have no parent) and b) are related to a specific archival institution record.

If you need further criteria for the select to target the right records, you'll have to figure that out! We can provide some general suggestions if you're stuck but you'll need to figure out the exact query parameters you need yourself.

After performing the SQL update query, I recommend running some common maintenance tasks to ensure everything is updated properly. 

First, let's rebuild the nested set. This is a model used to help manage hierarchical relationships in the relatively flat, table-based structure of a relational database. Since we have changed the hierarchy, we want to ensure that the nested set model matches. We can rebuild it using: 
We also want to ensure that our search index reflects the changes we've made, so let's re-populate that as well: 
Last, we want to ensure we are seeing our updates, and not previously cached content that may be out of date. Let's clear the application cache. We'll also restart PHP-FPM, which has its own cache. 

To clear the Symfony cache: 
The command for restarting PHP-FPM will depend on your installed PHP version. If you are using AtoM 2.6 with PHP 7.2 on Ubuntu 18.04 as recommended in our installation docs, you can restart PHP-FPM with:
Good luck! Let us know how it goes. 

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/81e7ddf1-7543-4acc-b2a4-6071e4329571n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages