Hi all, How many of you working on deployments are wrestling with this fundamental question: Where are the collection objects physically located right now? For our deployment for the Phoebe A. Hearst Museum of Anthropology, we have spent many, many hours working around how "current locations" are handled in CSpace. The human eye can see where things are by looking at the potentially multiple Location/Movement/Inventory records, but we need to build reports and other things (even little web applications) that are based on this information. How have you worked through this for your collection? We are actually thinking about adding "current location" field(s) to our extension schema on cataloging and maintaining them using database triggers. It sounds ugly but we might have no choice. Do you have any other ideas? Thanks, Chris Chris Hoffman, Ph.D. Manager of Informatics Services IST-Research & Content Technologies, UC Berkeley chris.hoffman@berkeley.edu 510-642-9643

Hi Chris, We have also discussed this many times and have settled on an approach something like this... We define the current location as "the related movement record containing the most recent movement date, no later than today, where the removal date is null". The idea being, that when an object is moved, the removal date is filled out and the record is hard saved. That movement record is then considered a past location. A new movement record is then created, which becomes the current location. To look up this value for report creation, I think the sql will require joining at least the movements_commmon, collectionobjects_common, relations_common and hierarchy tables - plus the logic in the paragraph above. There may be more needed (to support multiple tenants?), I'm not sure. But either way, it's sure a lot of sql to read a single value. I just tried to write it, but failed (I'll retry when I return next week). I'd also like to know if there's a easier way to accomplish this. Cheers, Chris P ________________________________ Fra: talk-bounces@lists.collectionspace.org [mailto:talk-bounces@lists.collectionspace.org] På vegne af Chris Hoffman Sendt: 8. august 2012 23:14 Til: CollectionSpace Talk List Emne: [Talk] Current storage location question Hi all, How many of you working on deployments are wrestling with this fundamental question: Where are the collection objects physically located right now? For our deployment for the Phoebe A. Hearst Museum of Anthropology, we have spent many, many hours working around how "current locations" are handled in CSpace. The human eye can see where things are by looking at the potentially multiple Location/Movement/Inventory records, but we need to build reports and other things (even little web applications) that are based on this information. How have you worked through this for your collection? We are actually thinking about adding "current location" field(s) to our extension schema on cataloging and maintaining them using database triggers. It sounds ugly but we might have no choice. Do you have any other ideas? Thanks, Chris Chris Hoffman, Ph.D. Manager of Informatics Services IST-Research & Content Technologies, UC Berkeley chris.hoffman@berkeley.edu<mailto:chris.hoffman@berkeley.edu> 510-642-9643

Dear Chris P, Thanks for your response; and your approach is exactly the approach we have used so far for PAHMA. We have written several sql queries to find the related movement record smeeting the criteria you suggested. This works, but is very slow: it can take many minutes to perform the query on the 700K+ PAHMA database (which has about 2 million current and legacy movement records). While it is philosophically elegant to represent the location of an object as a relationship between the object and event sin which it was moved, it is operationally challenging to have this be the normal means to determine where it is. Indeed, looking at the standard collectionobject UI display, one would be hard-pressed to find the current location of the object. We would be delighted to share some sample sql to do this with you if you like. Regards, John John B. Lowe, PhD www.johnblowe.com On Thu, August 9, 2012 6:20 am, Christopher Pott wrote: > Hi Chris, > > We have also discussed this many times and have settled on an approach > something like this... > > We define the current location as "the related movement record containing > the most recent movement date, no later than today, where the removal date > is null". The idea being, that when an object is moved, the removal date > is filled out and the record is hard saved. That movement record is then > considered a past location. A new movement record is then created, which > becomes the current location. > > To look up this value for report creation, I think the sql will require > joining at least the movements_commmon, collectionobjects_common, > relations_common and hierarchy tables - plus the logic in the paragraph > above. There may be more needed (to support multiple tenants?), I'm not > sure. But either way, it's sure a lot of sql to read a single value. I > just tried to write it, but failed (I'll retry when I return next week). > I'd also like to know if there's a easier way to accomplish this. > > Cheers, > Chris P > > ________________________________ > Fra: talk-bounces@lists.collectionspace.org > [mailto:talk-bounces@lists.collectionspace.org] På vegne af Chris Hoffman > Sendt: 8. august 2012 23:14 > Til: CollectionSpace Talk List > Emne: [Talk] Current storage location question > > Hi all, > > How many of you working on deployments are wrestling with this fundamental > question: Where are the collection objects physically located right now? > > For our deployment for the Phoebe A. Hearst Museum of Anthropology, we > have spent many, many hours working around how "current locations" are > handled in CSpace. The human eye can see where things are by looking at > the potentially multiple Location/Movement/Inventory records, but we need > to build reports and other things (even little web applications) that are > based on this information. How have you worked through this for your > collection? > > We are actually thinking about adding "current location" field(s) to our > extension schema on cataloging and maintaining them using database > triggers. It sounds ugly but we might have no choice. Do you have any > other ideas? > > Thanks, > Chris > > Chris Hoffman, Ph.D. > Manager of Informatics Services > IST-Research & Content Technologies, UC Berkeley > chris.hoffman@berkeley.edu<mailto:chris.hoffman@berkeley.edu> > 510-642-9643 > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >

Hi John (and all), Is this a problem that could be addressed - at least in the short term - by a database view? What other approaches, other than occasionally generating time-consuming reports, have you been thinking about? Was looking naively at this ... http://www.revsys.com/blog/2006/jan/03/when-to-use-a-materialized-view-in-postgresql/ Aron On Thu, Aug 9, 2012 at 7:35 AM, <jblowe@berkeley.edu> wrote: > Dear Chris P, > > Thanks for your response; and your approach is exactly the approach we > have used so far for PAHMA. > > We have written several sql queries to find the related movement record > smeeting the criteria you suggested. This works, but is very slow: it can > take many minutes to perform the query on the 700K+ PAHMA database (which > has about 2 million current and legacy movement records). > > While it is philosophically elegant to represent the location of an object > as a relationship between the object and event sin which it was moved, it > is operationally challenging to have this be the normal means to determine > where it is. Indeed, looking at the standard collectionobject UI display, > one would be hard-pressed to find the current location of the object. > > We would be delighted to share some sample sql to do this with you if you > like. > > Regards, > > John > > John B. Lowe, PhD > www.johnblowe.com > > > On Thu, August 9, 2012 6:20 am, Christopher Pott wrote: >> Hi Chris, >> >> We have also discussed this many times and have settled on an approach >> something like this... >> >> We define the current location as "the related movement record containing >> the most recent movement date, no later than today, where the removal date >> is null". The idea being, that when an object is moved, the removal date >> is filled out and the record is hard saved. That movement record is then >> considered a past location. A new movement record is then created, which >> becomes the current location. >> >> To look up this value for report creation, I think the sql will require >> joining at least the movements_commmon, collectionobjects_common, >> relations_common and hierarchy tables - plus the logic in the paragraph >> above. There may be more needed (to support multiple tenants?), I'm not >> sure. But either way, it's sure a lot of sql to read a single value. I >> just tried to write it, but failed (I'll retry when I return next week). >> I'd also like to know if there's a easier way to accomplish this. >> >> Cheers, >> Chris P >> >> ________________________________ >> Fra: talk-bounces@lists.collectionspace.org >> [mailto:talk-bounces@lists.collectionspace.org] På vegne af Chris Hoffman >> Sendt: 8. august 2012 23:14 >> Til: CollectionSpace Talk List >> Emne: [Talk] Current storage location question >> >> Hi all, >> >> How many of you working on deployments are wrestling with this fundamental >> question: Where are the collection objects physically located right now? >> >> For our deployment for the Phoebe A. Hearst Museum of Anthropology, we >> have spent many, many hours working around how "current locations" are >> handled in CSpace. The human eye can see where things are by looking at >> the potentially multiple Location/Movement/Inventory records, but we need >> to build reports and other things (even little web applications) that are >> based on this information. How have you worked through this for your >> collection? >> >> We are actually thinking about adding "current location" field(s) to our >> extension schema on cataloging and maintaining them using database >> triggers. It sounds ugly but we might have no choice. Do you have any >> other ideas? >> >> Thanks, >> Chris >> >> Chris Hoffman, Ph.D. >> Manager of Informatics Services >> IST-Research & Content Technologies, UC Berkeley >> chris.hoffman@berkeley.edu<mailto:chris.hoffman@berkeley.edu> >> 510-642-9643 >> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >> > > > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org

Aron, This looks very interesting, thanks! I think we've been a bit reluctant to build additional tables and views simply because they would represent another layer of stuff to customize and maintain. However, this approach (coupled with the approaches suggested in a link found on the page you sent http://media.revsys.com/talks/djangocon/2009/sing.pdf) could be just the ticket. Looking forward to further progress, John On Thu, August 9, 2012 8:08 am, Aron Roberts wrote: > Hi John (and all), > > Is this a problem that could be addressed - at least in the short > term - by a database view? What other approaches, other than > occasionally generating time-consuming reports, have you been thinking > about? > > Was looking naively at this ... > > http://www.revsys.com/blog/2006/jan/03/when-to-use-a-materialized-view-in-postgresql/ > > Aron > > On Thu, Aug 9, 2012 at 7:35 AM, <jblowe@berkeley.edu> wrote: >> Dear Chris P, >> >> Thanks for your response; and your approach is exactly the approach we >> have used so far for PAHMA. >> >> We have written several sql queries to find the related movement record >> smeeting the criteria you suggested. This works, but is very slow: it >> can >> take many minutes to perform the query on the 700K+ PAHMA database >> (which >> has about 2 million current and legacy movement records). >> >> While it is philosophically elegant to represent the location of an >> object >> as a relationship between the object and event sin which it was moved, >> it >> is operationally challenging to have this be the normal means to >> determine >> where it is. Indeed, looking at the standard collectionobject UI >> display, >> one would be hard-pressed to find the current location of the object. >> >> We would be delighted to share some sample sql to do this with you if >> you >> like. >> >> Regards, >> >> John >> >> John B. Lowe, PhD >> www.johnblowe.com >> >> >> On Thu, August 9, 2012 6:20 am, Christopher Pott wrote: >>> Hi Chris, >>> >>> We have also discussed this many times and have settled on an approach >>> something like this... >>> >>> We define the current location as "the related movement record >>> containing >>> the most recent movement date, no later than today, where the removal >>> date >>> is null". The idea being, that when an object is moved, the removal >>> date >>> is filled out and the record is hard saved. That movement record is >>> then >>> considered a past location. A new movement record is then created, >>> which >>> becomes the current location. >>> >>> To look up this value for report creation, I think the sql will require >>> joining at least the movements_commmon, collectionobjects_common, >>> relations_common and hierarchy tables - plus the logic in the paragraph >>> above. There may be more needed (to support multiple tenants?), I'm not >>> sure. But either way, it's sure a lot of sql to read a single value. I >>> just tried to write it, but failed (I'll retry when I return next >>> week). >>> I'd also like to know if there's a easier way to accomplish this. >>> >>> Cheers, >>> Chris P >>> >>> ________________________________ >>> Fra: talk-bounces@lists.collectionspace.org >>> [mailto:talk-bounces@lists.collectionspace.org] På vegne af Chris >>> Hoffman >>> Sendt: 8. august 2012 23:14 >>> Til: CollectionSpace Talk List >>> Emne: [Talk] Current storage location question >>> >>> Hi all, >>> >>> How many of you working on deployments are wrestling with this >>> fundamental >>> question: Where are the collection objects physically located right >>> now? >>> >>> For our deployment for the Phoebe A. Hearst Museum of Anthropology, we >>> have spent many, many hours working around how "current locations" are >>> handled in CSpace. The human eye can see where things are by looking >>> at >>> the potentially multiple Location/Movement/Inventory records, but we >>> need >>> to build reports and other things (even little web applications) that >>> are >>> based on this information. How have you worked through this for your >>> collection? >>> >>> We are actually thinking about adding "current location" field(s) to >>> our >>> extension schema on cataloging and maintaining them using database >>> triggers. It sounds ugly but we might have no choice. Do you have any >>> other ideas? >>> >>> Thanks, >>> Chris >>> >>> Chris Hoffman, Ph.D. >>> Manager of Informatics Services >>> IST-Research & Content Technologies, UC Berkeley >>> chris.hoffman@berkeley.edu<mailto:chris.hoffman@berkeley.edu> >>> 510-642-9643 >>> >>> _______________________________________________ >>> Talk mailing list >>> Talk@lists.collectionspace.org >>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>> >> >> >> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >

I'd also like to point out that John Lowe built a Postgres function "findcurrentlocation" that takes the object's CSID as a parameter and returns the current storage location, using the logic that Chris Pott described. That allows us to build SQL queries (for reports or otherwise) and include that function as if it were just another field in the query. Very handy! Thanks, Chris H On Aug 9, 2012, at 8:32 AM, jblowe@berkeley.edu wrote: > Aron, > > This looks very interesting, thanks! > > I think we've been a bit reluctant to build additional tables and views > simply because they would represent another layer of stuff to customize > and maintain. > > However, this approach (coupled with the approaches suggested in a link > found on the page you sent > http://media.revsys.com/talks/djangocon/2009/sing.pdf) could be just the > ticket. > > Looking forward to further progress, > > John > > On Thu, August 9, 2012 8:08 am, Aron Roberts wrote: >> Hi John (and all), >> >> Is this a problem that could be addressed - at least in the short >> term - by a database view? What other approaches, other than >> occasionally generating time-consuming reports, have you been thinking >> about? >> >> Was looking naively at this ... >> >> http://www.revsys.com/blog/2006/jan/03/when-to-use-a-materialized-view-in-postgresql/ >> >> Aron >> >> On Thu, Aug 9, 2012 at 7:35 AM, <jblowe@berkeley.edu> wrote: >>> Dear Chris P, >>> >>> Thanks for your response; and your approach is exactly the approach we >>> have used so far for PAHMA. >>> >>> We have written several sql queries to find the related movement record >>> smeeting the criteria you suggested. This works, but is very slow: it >>> can >>> take many minutes to perform the query on the 700K+ PAHMA database >>> (which >>> has about 2 million current and legacy movement records). >>> >>> While it is philosophically elegant to represent the location of an >>> object >>> as a relationship between the object and event sin which it was moved, >>> it >>> is operationally challenging to have this be the normal means to >>> determine >>> where it is. Indeed, looking at the standard collectionobject UI >>> display, >>> one would be hard-pressed to find the current location of the object. >>> >>> We would be delighted to share some sample sql to do this with you if >>> you >>> like. >>> >>> Regards, >>> >>> John >>> >>> John B. Lowe, PhD >>> www.johnblowe.com >>> >>> >>> On Thu, August 9, 2012 6:20 am, Christopher Pott wrote: >>>> Hi Chris, >>>> >>>> We have also discussed this many times and have settled on an approach >>>> something like this... >>>> >>>> We define the current location as "the related movement record >>>> containing >>>> the most recent movement date, no later than today, where the removal >>>> date >>>> is null". The idea being, that when an object is moved, the removal >>>> date >>>> is filled out and the record is hard saved. That movement record is >>>> then >>>> considered a past location. A new movement record is then created, >>>> which >>>> becomes the current location. >>>> >>>> To look up this value for report creation, I think the sql will require >>>> joining at least the movements_commmon, collectionobjects_common, >>>> relations_common and hierarchy tables - plus the logic in the paragraph >>>> above. There may be more needed (to support multiple tenants?), I'm not >>>> sure. But either way, it's sure a lot of sql to read a single value. I >>>> just tried to write it, but failed (I'll retry when I return next >>>> week). >>>> I'd also like to know if there's a easier way to accomplish this. >>>> >>>> Cheers, >>>> Chris P >>>> >>>> ________________________________ >>>> Fra: talk-bounces@lists.collectionspace.org >>>> [mailto:talk-bounces@lists.collectionspace.org] På vegne af Chris >>>> Hoffman >>>> Sendt: 8. august 2012 23:14 >>>> Til: CollectionSpace Talk List >>>> Emne: [Talk] Current storage location question >>>> >>>> Hi all, >>>> >>>> How many of you working on deployments are wrestling with this >>>> fundamental >>>> question: Where are the collection objects physically located right >>>> now? >>>> >>>> For our deployment for the Phoebe A. Hearst Museum of Anthropology, we >>>> have spent many, many hours working around how "current locations" are >>>> handled in CSpace. The human eye can see where things are by looking >>>> at >>>> the potentially multiple Location/Movement/Inventory records, but we >>>> need >>>> to build reports and other things (even little web applications) that >>>> are >>>> based on this information. How have you worked through this for your >>>> collection? >>>> >>>> We are actually thinking about adding "current location" field(s) to >>>> our >>>> extension schema on cataloging and maintaining them using database >>>> triggers. It sounds ugly but we might have no choice. Do you have any >>>> other ideas? >>>> >>>> Thanks, >>>> Chris >>>> >>>> Chris Hoffman, Ph.D. >>>> Manager of Informatics Services >>>> IST-Research & Content Technologies, UC Berkeley >>>> chris.hoffman@berkeley.edu<mailto:chris.hoffman@berkeley.edu> >>>> 510-642-9643 >>>> >>>> _______________________________________________ >>>> Talk mailing list >>>> Talk@lists.collectionspace.org >>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>>> >>> >>> >>> >>> _______________________________________________ >>> Talk mailing list >>> Talk@lists.collectionspace.org >>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >> > > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org

Hi John & Chris, It's interesting for us to hear about your performance challenges as we also have a great deal of movement history to import. It also concerns us that this approach demands precise data entry - for instance it's possible to create many "current locations" for a single object. But for now at least it seems like the way to go. I'd like to take you up on your offer to share some of your reporting sql. I was thinking that if we could begin to build a shared library of this kind of supporting code (maybe on the cspace wiki, or some other shared space), it could be of great benefit to the community - everybody is going to need this sooner or later. Cheers, Chris P > -----Oprindelig meddelelse----- > Fra: jblowe@berkeley.edu [mailto:jblowe@berkeley.edu] > Sendt: 9. august 2012 16:36 > Til: Christopher Pott > Cc: CollectionSpace Talk List > Emne: Re: [Talk] Current storage location question > > Dear Chris P, > > Thanks for your response; and your approach is exactly the > approach we have used so far for PAHMA. > > We have written several sql queries to find the related > movement record smeeting the criteria you suggested. This > works, but is very slow: it can take many minutes to perform > the query on the 700K+ PAHMA database (which has about 2 > million current and legacy movement records). > > While it is philosophically elegant to represent the location > of an object as a relationship between the object and event > sin which it was moved, it is operationally challenging to > have this be the normal means to determine where it is. > Indeed, looking at the standard collectionobject UI display, > one would be hard-pressed to find the current location of the object. > > We would be delighted to share some sample sql to do this > with you if you like. > > Regards, > > John > > John B. Lowe, PhD > www.johnblowe.com > > > On Thu, August 9, 2012 6:20 am, Christopher Pott wrote: > > Hi Chris, > > > > We have also discussed this many times and have settled on > an approach > > something like this... > > > > We define the current location as "the related movement record > > containing the most recent movement date, no later than > today, where > > the removal date is null". The idea being, that when an object is > > moved, the removal date is filled out and the record is hard saved. > > That movement record is then considered a past location. A new > > movement record is then created, which becomes the current location. > > > > To look up this value for report creation, I think the sql will > > require joining at least the movements_commmon, > > collectionobjects_common, relations_common and hierarchy > tables - plus > > the logic in the paragraph above. There may be more needed > (to support > > multiple tenants?), I'm not sure. But either way, it's sure > a lot of > > sql to read a single value. I just tried to write it, but > failed (I'll retry when I return next week). > > I'd also like to know if there's a easier way to accomplish this. > > > > Cheers, > > Chris P > > > > ________________________________ > > Fra: talk-bounces@lists.collectionspace.org > > [mailto:talk-bounces@lists.collectionspace.org] På vegne af Chris > > Hoffman > > Sendt: 8. august 2012 23:14 > > Til: CollectionSpace Talk List > > Emne: [Talk] Current storage location question > > > > Hi all, > > > > How many of you working on deployments are wrestling with this > > fundamental > > question: Where are the collection objects physically > located right now? > > > > For our deployment for the Phoebe A. Hearst Museum of > Anthropology, we > > have spent many, many hours working around how "current > locations" are > > handled in CSpace. The human eye can see where things are > by looking > > at the potentially multiple Location/Movement/Inventory > records, but > > we need to build reports and other things (even little web > > applications) that are based on this information. How have > you worked > > through this for your collection? > > > > We are actually thinking about adding "current location" > field(s) to > > our extension schema on cataloging and maintaining them using > > database triggers. It sounds ugly but we might have no choice. Do > > you have any other ideas? > > > > Thanks, > > Chris > > > > Chris Hoffman, Ph.D. > > Manager of Informatics Services > > IST-Research & Content Technologies, UC Berkeley > > chris.hoffman@berkeley.edu<mailto:chris.hoffman@berkeley.edu> > > 510-642-9643 > > > > _______________________________________________ > > Talk mailing list > > Talk@lists.collectionspace.org > > > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectio > > nspace.org > > > > >