Relaciones entre tablas

129 views

Skip to first unread message

leonardo...@gmail.com

unread,

Mar 11, 2020, 8:25:51 AM3/11/20

to AtoM Users

Saludos, necesito realizar una Query que me permita eliminar datos en la tabla term_i18n y esos mismos datos crearlos en actors_i18n. El problema es que no sé que campo de la tabla se relacionan. También quisiera saber si estas dos tablas se relacionan con information_object_i18n. La idea es hacer una modificacion que se creo de manera erronea en term_i18n y crearlas en actors_i18n y se reflen correctamente los datos.

Dan Gillean

unread,

Mar 11, 2020, 6:23:19 PM3/11/20

to ICA-AtoM Users

Hi Leonardo,

I'm not sure we will be able to provide such a query, as there are a lot of variables that would be specific to your needs. In fact, this has come up in the forum in the past, and we were unable to provide an easy solution.

However, if you want to try to craft your own SQL query, I can help explain how the data model is organized, and hopefully this will help point you in the right direction. If you do decide to try, please MAKE SURE YOU BACKUP YOUR DATA FIRST!

I've recently added a new Entity Relationship Diagram to our wiki, which will give you an up-to-date overview of the tables in AtoM, and how they are related:

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

While it is very general and does not cover all entities, our high-level entity model diagram might also be useful:

Data model overview

First, at the database level, pretty much every entity in AtoM has a relation to the object table - this is where initial object IDs are generated and stored when records are created.

AtoM was developed to be fully translatable, so while main entity details (such as ID) are linked to the object table and stored in the primary entity table (for example, the term table for terms), any form field that is translatable will generally be found in the related i18n (short for internationalization) table - so a term's authorized form of name, its scope note and source note, etc will be stored in the term_i18n table, while certain relationships will be stored in the term table - such as the ID of the taxonomy the term belongs to; the ID of any parent term if they are organized hierarchically, etc. Also the source culture - the original culture or language that the term was first created in - will be stored in the term table as an ISO 639-1 two-letter language code (for example, en, fr, es, ca, etc), but the actual descriptive content is in the term_i18n table.

Terms are managed in taxonomies. So every term belongs to a taxonomy - and again, the ID of the related taxonomy is stored in the term table. A taxonomy can have many terms, but a term can only belong in one taxonomy.

Terms provide controlled values for many other entities - almost every time you see a drop-down menu in one of the metadata entry templates (for example, selecting the Level of description on an archival description, or the Entity type [person, family, or corporate body] on an authority record) you are creating a relationship between a term and that entity in the database. Some of these are more obvious than others - for example, Subject and Place access points. When you add a Subject access point to a description, it's clear you are making a relation between an information object and a term. Others, like the level of description, are less obvious - they seem from a user perspective like they are part of the archival description, but they are still terms in a taxonomy - these values are not stored in the information_object tables.

We relate terms to other entities via the object_term_relation table - it will have the ID of the object (for example a description, an authority record, an archival instituiton - or as they are called in the data schema, an information object, an actor, and a repository), and the ID of the related term.

Note that we also have a relation and relation_i18n table - this is only used to manage actor-actor relationships, which can have dates, a description, a type, etc.

Authority record data is primarily stored in the actor and actor_i18n table. Note that the repository table has a relation to the actor table - in the early data models, a repository was considered a special type of actor. In fact, I believe that the user accounts also make use of the actor table, which is one of the reasons you want to be very careful about how you alter or insert data here!

Actors (such as creators) are typically related to archival descriptions (information objects) via events. Think of it this way - a creation event indicates that a record (generally described by an archival description) was created within a certain time frame (start and end dates) by a specific actor (represented by an authority record. So in the data model, the actor table links to the event table, which in turn links to the information_object table. Additional free text fields associated with an event (such as the free-text display date, the description field shown in some of the templates for events, etc) are in the event_i18n table, since they are translatable. Events also have a type (creation, accumulation, contribution, broadcast, manufacture, etc) - and those are terms in the Event types taxonomy, so the event table will have a term_id value, in addition to the actor_id and object_id values for the related authority and description.

Query building and consideration

Some things you will need to consider and/or find to be able to construct a query:

First, you don't want to move ALL terms in your database to the actor table - terms exist in many different taxonomies. Hopefully the terms you want to move are all in one taxonomy, such as the Subjects.

To be able to select the correct terms then, we will need to know the related taxonomy ID. We can get a list, with the name from the taxonomy, like so (I've narrowed the results to show just the English name, but you could change the culture code to something else if preferred):

SELECT id, name FROM taxonomy_i18n WHERE culture='en';

Now we get something like this:

+----+---------------------------------------+
| id | name |
+----+---------------------------------------+
| 30 | NULL |
| 31 | Description Detail Levels |
| 32 | Actor Entity Types |
| 33 | Description Statuses |
| 34 | Levels of description |
| 35 | Subjects |
| 36 | Actor Name Types |
| 37 | Note types |
| 38 | Repository Types |
| 40 | Event Types |
| 41 | Qubit Setting Labels |
| 42 | Places |
| 43 | ISDF Function Types |
| 44 | Historical Events |
| 45 | Collection Types |
| 46 | Media Types |
| 47 | Digital Object Usages |
| 48 | Physical Object Type |
| 49 | Relation Type |
| 50 | Material Type |
| 51 | RAD Note |
| 52 | RAD Title Note |
| 53 | MODS Resource Type |
| 54 | Dublin Core Types |
| 55 | Actor Relation Type |
| 56 | Relation Note Types |
| 57 | Term Relation Types |
| 59 | Status Types |
| 60 | Publication Status |
| 61 | Function Relation Type |
| 62 | Accession resource type |
| 63 | Accession acquisition type |
| 64 | Accession processing priority |
| 65 | Accession processing status |
| 66 | Deaccession scope |
| 67 | Rights act |
| 68 | Rights basis |
| 69 | Copyright status |
| 70 | Information object templates |
| 71 | AIP types |
| 72 | Thematic Area |
| 73 | Geographic Subregion |
| 74 | DACS Note |
| 75 | PREMIS Rights Statutes |
| 78 | Genre |
| 79 | Job status |
| 80 | Actor occupations |
| 81 | User actions |
| 82 | Accession alternative identifier type |
+----+---------------------------------------+

If we just want the ID of one specific taxonomy, we can use it's name, like this example, using the Genre taxonomy:

SELECT id FROM taxonomy_i18n WHERE culture = 'en' AND name = 'Genre';

+----+
| id |
+----+
| 78 |
+----+

Once we know the ID of a taxonomy, we can also get all the IDs of any terms in that taxonomy. For example, for Levels of description, which returned an ID of 34 above:

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 |
+---------+--------------+
| 193 | Sous-fonds |
| 194 | Collection |
| 195 | Series |
| 196 | Subseries |
| 197 | File |
| 198 | Item |
| 69884 | Record group |
| 2000004 | Part |
| 2002760 | Fonds |
+---------+--------------+

If you only wanted to move SOME terms in a taxonomy, but not all of them, then you would have to select them specifically by ID as part of your final query.

Now, remember, every entity in AtoM has a record in the object table. But that object table also defines the entity type - or the class, as it is called in the database:

SELECT DISTINCT class_name FROM object;

If you just moved your records without changing the object type as well, you would probably have issues! Terms use the QubitTerm class, while authority records use the QubitActor class.

I suspect that there is a lot more complexity involved here, which is one of the reasons I urge you to make backups before trying anything.

If you decide you'd rather just delete the terms in the taxonomy, and then create a CSV import of your authority records instead, we can use SQL to delete all the terms in a taxonomy. We need to know the ID of the taxonomy - let's use Subjects as an example (id=35). In this case, we delete from the object table, which cascades across other tables:

DELETE FROM object WHERE id IN (SELECT id FROM term WHERE taxonomy_id = 35);

There's probably a lot more to say here, but I am not a developer or a SQL expert, so this is about as far as I can go! If you want to see other posts in the forum that involve SQL, you can browse by tags - here are all threads with a SQL tag:

https://groups.google.com/forum/#!tags/ica-atom-users/SQL

I hope it helps!

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

@accesstomemory

he / him

On Wed, Mar 11, 2020 at 8:25 AM <leonardo...@gmail.com> wrote:

Saludos, necesito realizar una Query que me permita eliminar datos en la tabla term_i18n y esos mismos datos crearlos en actors_i18n. El problema es que no sé que campo de la tabla se relacionan. También quisiera saber si estas dos tablas se relacionan con information_object_i18n. La idea es hacer una modificacion que se creo de manera erronea en term_i18n y crearlas en actors_i18n y se reflen correctamente los datos.

--
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/c38b7c0f-5af8-40d2-8d1f-c163de75a26c%40googlegroups.com.

Dan Gillean

unread,

Mar 11, 2020, 6:27:17 PM3/11/20

to ICA-AtoM Users

PS, I forgot to include this, but:

We have an introduction to constructing SQL queries in AtoM in the following slide deck, in case it helps:

https://www.slideshare.net/accesstomemory/constructing-sql-queries-for-atom

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

@accesstomemory

he / him

Reply all

Reply to author

Forward

0 new messages