Hi Rodolfo,
Unfortunately, we do not currently have such a task. It could likely be done with SQL, but it will take some research and testing to figure out how exactly. I don't have a query prepared for you, but I can point you to some of the pieces if you have some basic SQL familiarity!
First, some basic resources:
- How to access the MySQL command prompt:
- How to back up your database - do NOT use SQL without doing this first, just in case!
- Where to find the latest Entity Relationship Diagram (describing AtoM's database schema):
- A few basic query examples to see lists of term IDs and taxonomy IDs, etc:
A very brief and incomplete overview of AtoM's database schema:
- Because AtoM is a multilingual application, most entities have 2 tables - a base table with fields that don't need translations (such as identifiers, etc), and another table where any string that can be translated lives - this second table will have the same name, but with _i18n appended to the name. This is a common developer shorthand for "internationalization" (because that word is 18 characters long and devs love shortcuts). So for example here are some of the main entities:
- Descriptions: information_object and information_object_i18n
- Authority record: actor and actor_i18n
- Accessions: accession and accession_i18n
- Archival institution: repository and repository_i18n
- etc
- Despite the above, not ALL data from a single entity will necessarily be on these two tables alone. In many cases there are additional tables where data may be found. For example, there are note and note_i18n tables - so any field in descriptions, actors, terms, etc that is considered a "note" may in fact be stored in these tables and linked to the parent entity. Similarly, there are separate contact_information and contact_information_i18n tables that are used for donors, rightsholders, repositories, and any other entity that has a contact information section.
- There are also the property and property_i18n tables - these have been sort of used as key-value pair tables for fields that didn't easily fit into the existing database tables. For example, the description tables were originally modeled around ISAD fields - but the Canadian RAD standard has many fields that don't map easily to the international description standard, so a number of RAD's more unique fields are stored in the property tables instead.
- There are tables for taxonomies, and for the terms in a taxonomy. The term table has a taxonomy_id field to link it to the taxonomy to which it belongs
- Most main entities in AtoM, including terms, are linked to a base object table. The object table holds the unique ID (i.e. the object ID) of each record - usually this is the key on other tables. So for example, the ID field on the term table will typically be the same ID of the related entry on the object table.
- Here's the complicated part - because terms can relate to multiple different entities, instead of having a foreign key directly on the terms table, we have a separate object_term_relation table. Each row will have its own ID, but also an object_id (of the related entity) and a term_id (of the related term) to link them all together. I suspect this table will be important to you, so you can select the right terms for deletion.
Also note: as a MySQL database, you can get AtoM's db to work with a graphical tool like MySQL Workbench, or PHPMyAdmin, if you prefer a UI to explore and construct queries.
So, tables you will probably need in your join:
- object
- term and term_i18n
- taxonomy
- object_term_relation
Remember to make a backup before trying any SQL operation that will make changes! I would try to make your DELETE query from the object table, as I believe that this will cascade to other tables, so you don't have to perform deletes on all those tables that might have parts of the related term data. You will likely need the other tables when making your JOINs in the query, to properly select the right records that don't have entries in the object_term_relation table.
We DO have a sample query in the docs that can be used to check how many times a particular term (once you know its ID) is used as an access point in a particular culture - this example may also help you construct the correct JOINS across tables. See:
That is about as much help as I can personally offer at this point! I hope that this will be useful to you!
Cheers,