Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy?
Hi Dave, Thank you for the suggestion, which somehow had not occurred to me. I will do some experimenting with it this afternoon! I've identified 470 different resource records we have that potentially have this problem, so I'm absolutely looking for the most efficient way to fix this. Thanks! Olivia On Fri, Sep 20, 2019 at 9:21 AM Mayo, Dave wrote: > This is limited to _*published*_ AOs, so if you want to get all of them, > you’d probably need to use something like > https://archivesspace.github.io/archivesspace/api/#get-a-resource-tree > and then walk down the tree and get uris/ids manually. > > > > -- > > Dave Mayo (he/him) > > Senior Digital Library Software Engineer > Harvard University > HUIT > LTS > > > > *From: * on > behalf of "Mayo, Dave" > *Reply-To: *Archivesspace Users Group < > archivesspace_users_group@lyralists.lyrasis.org> > *Date: *Friday, September 20, 2019 at 10:13 AM > *To: *Archivesspace Users Group < > archivesspace_users_group@lyralists.lyrasis.org> > *Subject: *Re: [Archivesspace_Users_Group] Database query to recreate > inventory hierarchy? > > > > No joy then, I’m sorry to say. Honestly, since the API is going to be in > the mix to use eventually, I’d suggest looking at this endpoint: > > > https://archivesspace.github.io/archivesspace/api/#get-the-list-of-uris-of-this-published-resource-and-all-published-archival-objects-contained-within-ordered-by-tree-order-i-e-if-you-fully-expanded-the-record-tree-and-read-from-top-to-bottom > <https://urldefense.proofpoint.com/v2/url?u=https-3A__archivesspace.github.io_archivesspace_api_-23get-2Dthe-2Dlist-2Dof-2Duris-2Dof-2Dthis-2Dpublished-2Dresource-2Dand-2Dall-2Dpublished-2Darchival-2Dobjects-2Dcontained-2Dwithin-2Dordered-2Dby-2Dtree-2Dorder-2Di-2De-2Dif-2Dyou-2Dfully-2Dexpanded-2Dthe-2Drecord-2Dtree-2Dand-2Dread-2Dfrom-2Dtop-2Dto-2Dbottom=DwMGaQ=WO-RGvefibhHBZq3fL85hQ=_Mv1dY22K7jvT5MD7xjbvGVzRDOUMhx4WYcnPSIzYnE=2fMESNfVzsOLpA4jhFdQmTe1ChlVGDyaYYraUA2CpB0=RXCUneIYE1hDsWDlAZ08krr_Xa8JXXiagrBveWRqSDc=> > > The API is probably the easiest way to get this information, though if > you’re fetching this info for _*all*_ records it’s definitely gonna be > slower. > > > > -- > > Dave Mayo (he/him) > > Senior Digital Library Software Engineer > Harvard University > HUIT > LTS > > > > *From: * on > behalf of Olivia S Solis > *Reply-To: *Archivesspace Users Group < > archivesspace_users_group@lyralists.lyrasis.org> > *Date: *Friday, September 20, 2019 at 10:05 AM > *To: *Archivesspace Users Group < > archivesspace_users_group@lyralists.lyrasis.org> > *Subject: *Re: [Archivesspace_Users_Group] Database query to recreate > inventory hierarchy? > > > > 5.5.60 > ___ > Archivesspace_Users_Group mailing list > Archivesspace_Users_Group@lyralists.lyrasis.org > http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group > -- Olivia Solis, MSIS Metadata Coordinator Dolph Briscoe Center for American History The University of Texas at Austin 2300 Red River St. Stop D1100 Austin TX, 78712-1426 (512) 232-8013 ___ Archivesspace_Users_Group mailing list Archivesspace_Users_Group@lyralists.lyrasis.org http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy?
This is limited to _published_ AOs, so if you want to get all of them, you’d probably need to use something like https://archivesspace.github.io/archivesspace/api/#get-a-resource-tree and then walk down the tree and get uris/ids manually. -- Dave Mayo (he/him) Senior Digital Library Software Engineer Harvard University > HUIT > LTS From: on behalf of "Mayo, Dave" Reply-To: Archivesspace Users Group Date: Friday, September 20, 2019 at 10:13 AM To: Archivesspace Users Group Subject: Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy? No joy then, I’m sorry to say. Honestly, since the API is going to be in the mix to use eventually, I’d suggest looking at this endpoint: https://archivesspace.github.io/archivesspace/api/#get-the-list-of-uris-of-this-published-resource-and-all-published-archival-objects-contained-within-ordered-by-tree-order-i-e-if-you-fully-expanded-the-record-tree-and-read-from-top-to-bottom<https://urldefense.proofpoint.com/v2/url?u=https-3A__archivesspace.github.io_archivesspace_api_-23get-2Dthe-2Dlist-2Dof-2Duris-2Dof-2Dthis-2Dpublished-2Dresource-2Dand-2Dall-2Dpublished-2Darchival-2Dobjects-2Dcontained-2Dwithin-2Dordered-2Dby-2Dtree-2Dorder-2Di-2De-2Dif-2Dyou-2Dfully-2Dexpanded-2Dthe-2Drecord-2Dtree-2Dand-2Dread-2Dfrom-2Dtop-2Dto-2Dbottom=DwMGaQ=WO-RGvefibhHBZq3fL85hQ=_Mv1dY22K7jvT5MD7xjbvGVzRDOUMhx4WYcnPSIzYnE=2fMESNfVzsOLpA4jhFdQmTe1ChlVGDyaYYraUA2CpB0=RXCUneIYE1hDsWDlAZ08krr_Xa8JXXiagrBveWRqSDc=> The API is probably the easiest way to get this information, though if you’re fetching this info for _all_ records it’s definitely gonna be slower. -- Dave Mayo (he/him) Senior Digital Library Software Engineer Harvard University > HUIT > LTS From: on behalf of Olivia S Solis Reply-To: Archivesspace Users Group Date: Friday, September 20, 2019 at 10:05 AM To: Archivesspace Users Group Subject: Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy? 5.5.60 ___ Archivesspace_Users_Group mailing list Archivesspace_Users_Group@lyralists.lyrasis.org http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy?
No joy then, I’m sorry to say. Honestly, since the API is going to be in the mix to use eventually, I’d suggest looking at this endpoint: https://archivesspace.github.io/archivesspace/api/#get-the-list-of-uris-of-this-published-resource-and-all-published-archival-objects-contained-within-ordered-by-tree-order-i-e-if-you-fully-expanded-the-record-tree-and-read-from-top-to-bottom The API is probably the easiest way to get this information, though if you’re fetching this info for _all_ records it’s definitely gonna be slower. -- Dave Mayo (he/him) Senior Digital Library Software Engineer Harvard University > HUIT > LTS From: on behalf of Olivia S Solis Reply-To: Archivesspace Users Group Date: Friday, September 20, 2019 at 10:05 AM To: Archivesspace Users Group Subject: Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy? 5.5.60 ___ Archivesspace_Users_Group mailing list Archivesspace_Users_Group@lyralists.lyrasis.org http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy?
Hi Dave, Thanks for your response! Looks like we're running MariaDB 5.5.60. On Fri, Sep 20, 2019 at 8:58 AM Mayo, Dave wrote: > It’s possible, but I think to do it in one query requires recursive common > table expressions – what version of MySQL/MariaDB are you on? > > I wrote a thing that’s almost this except I was getting container info > attached to the AOs rather than info from the AOs themselves, I can > probably find and repurpose it, but it won’t run on MySQL prior to 8.0. > > -- > > Dave Mayo (he/him) > > Senior Digital Library Software Engineer > Harvard University > HUIT > LTS > > > > *From: * on > behalf of Olivia S Solis > *Reply-To: *Archivesspace Users Group < > archivesspace_users_group@lyralists.lyrasis.org> > *Date: *Friday, September 20, 2019 at 9:48 AM > *To: *Archivesspace Users Group < > archivesspace_users_group@lyralists.lyrasis.org> > *Subject: *[Archivesspace_Users_Group] Database query to recreate > inventory hierarchy? > > > > Hi all, > > > > I have been trying to figure out a database query that will list all the > archival objects in a resource record's display_strings and their > associated ids in the order that they appear in the resource record. This > has proven more challenging than I thought. I can't find the magical query > that will leverage root_record_id (i.e. the resource record), parent_id, > and position. > > > > If anyone else has figured this out, please let me know! > > > > FYI, this is our use case, though I see many other uses for this query. We > publish our EAD to TARO, a consortia of Texas archives. TARO's style is to > only display the first container in a series of s with the same > container and omit containers until there is a new container. A lot of our > early EAD therefore has the first box in a series of s with the same > box. It looked fine in TARO! Not so much in ASpace. See screenshots below > for an example: > > > > TARO: > > [image: > https://lh4.googleusercontent.com/5P7wiGVFjswktn_sCsZ6TLIBzFP5jt29GKSGis3r52QMUUhNHlu0VKoNWOUwAerE-D4Qx4x5kY3tSG7zkENI4qIG3bdsrWzITbKCwBB4qBvmisiumoNv4nCe1IWO9uMkRq_3P7U] > > > > vs. EAD: > > [image: > https://lh3.googleusercontent.com/jqzk-JPuL819OETK2UZAJY_8ZFu9M56yeqYyydUOf7dF_YZJ90OHwdEjT6YU5637g6kEtAyIiv1zJpRpnh8mLRDkd8PjJKUr81iTwb08RfuagB3YqL0mFwfEQVUfCgtfIb2or8M] > > This problem affects thousands of archival objects, so manually fixing > this in the GUI would be extremely burdensome for us. I'd like to retrieve > archival objects in the order they appear in a resource record. In this > particular case, I'd also join the boxes and their IDs so that I could > either use fill down in OpenRefine or copy the box and paste is down in a > spreadsheet. Then I'd use the API to add the missing container instances. > But the essential problem I am having is that I just can't figure out the > database query to retrieve the AOs in order. > > > > Hopefully, this is clear. If anyone can help, let me know please! > > > > Thanks! > > Olivia > > > > -- > > Olivia Solis, MSIS > > Metadata Coordinator > > Dolph Briscoe Center for American History > > The University of Texas at Austin > > 2300 Red River St. Stop D1100 > > Austin TX, 78712-1426 > > (512) 232-8013 > ___ > Archivesspace_Users_Group mailing list > Archivesspace_Users_Group@lyralists.lyrasis.org > http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group > -- Olivia Solis, MSIS Metadata Coordinator Dolph Briscoe Center for American History The University of Texas at Austin 2300 Red River St. Stop D1100 Austin TX, 78712-1426 (512) 232-8013 ___ Archivesspace_Users_Group mailing list Archivesspace_Users_Group@lyralists.lyrasis.org http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
Re: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy?
It’s possible, but I think to do it in one query requires recursive common table expressions – what version of MySQL/MariaDB are you on? I wrote a thing that’s almost this except I was getting container info attached to the AOs rather than info from the AOs themselves, I can probably find and repurpose it, but it won’t run on MySQL prior to 8.0. -- Dave Mayo (he/him) Senior Digital Library Software Engineer Harvard University > HUIT > LTS From: on behalf of Olivia S Solis Reply-To: Archivesspace Users Group Date: Friday, September 20, 2019 at 9:48 AM To: Archivesspace Users Group Subject: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy? Hi all, I have been trying to figure out a database query that will list all the archival objects in a resource record's display_strings and their associated ids in the order that they appear in the resource record. This has proven more challenging than I thought. I can't find the magical query that will leverage root_record_id (i.e. the resource record), parent_id, and position. If anyone else has figured this out, please let me know! FYI, this is our use case, though I see many other uses for this query. We publish our EAD to TARO, a consortia of Texas archives. TARO's style is to only display the first container in a series of s with the same container and omit containers until there is a new container. A lot of our early EAD therefore has the first box in a series of s with the same box. It looked fine in TARO! Not so much in ASpace. See screenshots below for an example: TARO: [https://lh4.googleusercontent.com/5P7wiGVFjswktn_sCsZ6TLIBzFP5jt29GKSGis3r52QMUUhNHlu0VKoNWOUwAerE-D4Qx4x5kY3tSG7zkENI4qIG3bdsrWzITbKCwBB4qBvmisiumoNv4nCe1IWO9uMkRq_3P7U] vs. EAD: [https://lh3.googleusercontent.com/jqzk-JPuL819OETK2UZAJY_8ZFu9M56yeqYyydUOf7dF_YZJ90OHwdEjT6YU5637g6kEtAyIiv1zJpRpnh8mLRDkd8PjJKUr81iTwb08RfuagB3YqL0mFwfEQVUfCgtfIb2or8M] This problem affects thousands of archival objects, so manually fixing this in the GUI would be extremely burdensome for us. I'd like to retrieve archival objects in the order they appear in a resource record. In this particular case, I'd also join the boxes and their IDs so that I could either use fill down in OpenRefine or copy the box and paste is down in a spreadsheet. Then I'd use the API to add the missing container instances. But the essential problem I am having is that I just can't figure out the database query to retrieve the AOs in order. Hopefully, this is clear. If anyone can help, let me know please! Thanks! Olivia -- Olivia Solis, MSIS Metadata Coordinator Dolph Briscoe Center for American History The University of Texas at Austin 2300 Red River St. Stop D1100 Austin TX, 78712-1426 (512) 232-8013 ___ Archivesspace_Users_Group mailing list Archivesspace_Users_Group@lyralists.lyrasis.org http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
[Archivesspace_Users_Group] Database query to recreate inventory hierarchy?
Hi all, I have been trying to figure out a database query that will list all the archival objects in a resource record's display_strings and their associated ids in the order that they appear in the resource record. This has proven more challenging than I thought. I can't find the magical query that will leverage root_record_id (i.e. the resource record), parent_id, and position. If anyone else has figured this out, please let me know! FYI, this is our use case, though I see many other uses for this query. We publish our EAD to TARO, a consortia of Texas archives. TARO's style is to only display the first container in a series of s with the same container and omit containers until there is a new container. A lot of our early EAD therefore has the first box in a series of s with the same box. It looked fine in TARO! Not so much in ASpace. See screenshots below for an example: TARO: vs. EAD: This problem affects thousands of archival objects, so manually fixing this in the GUI would be extremely burdensome for us. I'd like to retrieve archival objects in the order they appear in a resource record. In this particular case, I'd also join the boxes and their IDs so that I could either use fill down in OpenRefine or copy the box and paste is down in a spreadsheet. Then I'd use the API to add the missing container instances. But the essential problem I am having is that I just can't figure out the database query to retrieve the AOs in order. Hopefully, this is clear. If anyone can help, let me know please! Thanks! Olivia -- Olivia Solis, MSIS Metadata Coordinator Dolph Briscoe Center for American History The University of Texas at Austin 2300 Red River St. Stop D1100 Austin TX, 78712-1426 (512) 232-8013 ___ Archivesspace_Users_Group mailing list Archivesspace_Users_Group@lyralists.lyrasis.org http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
