Display a list of recently updates to the db Atom

[Rick Hunter]

i need the query sql to Display a list of recently updates to the db Atom

[Dan Gillean]

Hi Rick, 

First, some general resources that might help: 

Check out our page of example AtoM SQL queries here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/
  

See an entity relationship diagram of AtoM's database schema here: 

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

Now, before I try to offer some suggestions for how SQL might help, I first wanted to point out some ways you might be able to get the information you need via the user interface. 

First, there is the Description updates page, available to administrators. See: 

• https://www.accesstomemory.org/docs/2.6/user-manual/administer/search-updates/
  

Additionally, the various search/browse result pages can help as well. By default, most pages have a sort button option called "Date modified." This is set as the default sort order for logged in users. It will show records that have been recently modified in the order they were modified. See: 

• https://www.accesstomemory.org/docs/latest/user-manual/access-content/navigate/#recurring-sort-button
  
• https://www.accesstomemory.org/docs/lateast/user-manual/administer/settings/#sort-browser-users
  

You can also try using Elasticsearch fields to create your own custom queries, based on modification dates. An example is given on slide 31 of the following deck for descriptions:

• https://www.slideshare.net/accesstomemory/searching-in-atom-103945925
  
• See also: https://www.accesstomemory.org/docs/latest/user-manual/access-content/advanced-search/#expert-searching-and-indexed-elasticsearch-fields

Other entities will have similar updatedAt fields in the search index, that could be used on their dedicated search/browse pages. 

Now, if this still doesn't provide what you need and you want to try querying MySQL directly, then... it will depend on what exactly you want to know. I'll provide some initial thoughts below, but there's not really one easy way to see all changes listed, or the contents of the changes. MySQL does have a binary log where all state changes are captured, so looking into that might be of interest, but is beyond the scope of what we can provide. As a starting point, see: 

• https://dev.mysql.com/doc/refman/8.0/en/binary-log.html
  
• https://dev.mysql.com/doc/refman/8.0/en/binlog.html
  

With all of this out of the way, here's what I can provide: 

All of AtoM's core entities are joined by having a base entry in the object table. This table includes created_at and updated_at columns that should give you a sense of when most updates have happened. You can also find out the slug of any individual update by doing the opposite of the example query described here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#finding-the-object-id-of-a-record
  

Every row in the object table will have an ID - this is the object_id value found in other tables, and is often used for the foreign key relationship. So, if you want to know the slug related to an update you see in the object table, you can use the ID value from the object table to query against the slug table, like so: 

• SELECT slug from slug WHERE object_id='XXXXX';

Replace XXXXX in the above example with the ID and hopefully that should work. 

Hopefully this helps!

Cheers, 

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

@accesstomemory

he / him

On Thu, Feb 25, 2021 at 8:38 PM Rick Hunter <ric...@gmail.com> wrote:

i need the query sql to Display a list of recently updates to the db Atom

--
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/ec4030e6-ac36-48bb-a24d-5ac8974ec976n%40googlegroups.com.