Changing the type of Events following CSV import

[Ad Axem]

Hi all,

Another question related to changing some values for all Items in an installation following a CSV import of several thousand Items.

Is there a way to change the type of Events in the database for all of these Items, from Creation to another (custom) value we will create in the Taxonomy? This follows a scope change from the client, who initially wanted these Events to be imported as Creation Events but now (following the import) would rather prefer to retain this information as Name Access Points or some other custom Event type, and import and associate a different set of Authority Records as Creators with the corresponding new Creation Events and Dates.

Hopefully, this could just be a matter of creating the custom Event type, look up its ID in the database and replace all ID values in the existing Events associated with all Items to the ID of the custom Event type, prior to any additional import. Would that work or is this area structured differently in the database?

In any case, many thanks in advance for any tips or pointers!

[Dan Gillean]

Hi! 

First, some warnings.

1: I am NOT A DEVELOPER. I am not a SQL expert. I have not tried the following myself. Make sure you make backups and proceed at your own risk!

2: The actual question you’ve asked - changing an event row into a relation with a name authority or a name access point - I don’t think is possible (or it requires scripting and is beyond the level of support we can provide via the forum). These relations are not stored as events. However, I believe it *would* be possible to change the creation events into another event type, including a new custom event type you add to the Event Types taxonomy.

Here goes.

Every time one of our developers shares some SQL, I have a file where I keep some notes in case they come in handy in the future. I think in this case I can get you most of the way there - though the final step to change the id you might have to figure out on your own. 

First, some general resources that should help you: 

We have some slides on working with SQL in AtoM here: 

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

The first couple slides show how you can configure a graphical interface (such as MySQL Workbench - though we've also used PHPMyAdmin) in the AtoM vagrant box - it should be possible to modify these steps to use the IP of your installation. I would recommend this for editing the IDs you will need to change - it should help you figure out what to do by providing a more graphical way to interact with the database. 

Second, the Entity Relationship Diagrams we have on our wiki might help you understand how the database is organized. See: 

• https://wiki.accesstomemory.org/Development/ERDs
  

Now, I have a saved query that I've modified that will return the term id and term name from a taxonomy - I've changed it to show me the English terms in the Event Types taxonomy: 

SELECT term.id, term_i18n.name FROM term LEFT JOIN term_i18n ON (term.id = term_i18n.id) WHERE term.taxonomy_id = (SELECT id FROM taxonomy_i18n WHERE culture = 'en' AND name = 'Event Types') AND term_i18n.culture = 'en';

I added a new term, called "Test term", before running this query - we can see in the table returned that it has an ID of 2003059: 

+---------+-------------------------+

| id      | name                    |

+---------+-------------------------+

|     111 | Creation                |

|     113 | Custody                 |

|     114 | Publication             |

|     115 | Contribution            |

|     117 | Collection              |

|     118 | Accumulation            |

|   89422 | Reproduction            |

| 2001121 | Distribution            |

| 2001142 | Broadcasting            |

| 2001156 | Manufacturing           |

| 2001189 | Record-keeping activity |

| 2003059 | Test term               |

+---------+-------------------------+

Notice that in my AtoM instance, Creation is 111. 

I then created a new description with 2 events to test this out - one with a creation event, and one with an event using my new "Test term". I used the RAD template so I could add an event description, to make sure I had the right terms. 

Now, using the query that our developer provided the other day for the events: 

select * from object 

join event on event.id = object.id

join event_i18n on event_i18n.id = object.id

where class_name = 'QubitEvent'

and created_at >= '2018-06-13 12:31:12';

We get a table returned that's too big to properly paste here, so let me add an image: 

​

We can see my 2 new events returned in the last 2 rows of that wide table. The first of the 2 is the creation event, and the second is my new "Test term" event. 

Comparing this with the table returned above, I can see that the term's ID in the first table matches the type_id in this joined table. This type_id lives in the event table: 

​

The results of this query suggests a few things that might help us: 

1. That the tables affected by an event are the object table, the event table, and the event_i18n tables
2. The value that I *think* you will need to change is the type_id in the event table, which corresponds to the term ID in the Event Types taxonomy.
3. Based on the JOIN in the last query, the event ID will the the same value as the object ID where they relate

I'm pretty sure that changing the type_id in the event table is all you'll need to do, once you've figured out the ID you want to use instead, and have determined how to find the right events. That is to say, event.type_id appears to be the foreign key to the term.id table - if you change the event.type_id to point to a different term.id, then the event should become a new term type. Hopefully using an interface like Workbench or PHPMyAdmin will help you accomplish the last step of actually changing the target events. 

Remember to make backups first!!! I would also suggest testing this on a single event row before you proceed and make bulk changes, to make sure it works as expected! 

Once you're done I would also suggest clearing the cache, restarting services, and re-indexing. 

If you do want to try to change events to access points, this is beyond what we can assist with via the forum. However, I would suggest this be handled as a programming task, not via SQL. I would suggest developing a PHP script that can be executed using the tools:run task, which would grab all the event info to an array or flat file, delete the events, and use the saved info to build new name access points. Good luck, and let us know how it goes! 

Regards, 

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

@accesstomemory

--
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-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/6fa38d82-faeb-42ad-baca-23a790f037af%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ad Axem]

Worked like a charm! :)

Thank you once again Dan, will let you know how the other issue (Creation Dates) goes as well.