YN
Yousuf Nejati
Thu, Jun 23, 2016 6:11 PM
Hello all,
I believe we got it figured out, turns out the postgres fragment was much
simpler than we thought.
To obtain the work terms used by a collection object, do:
SELECT
split_part(cwg.contentwork,'''',2) AS contentworks_s
FROM collectionobjects_common coc
LEFT OUTER JOIN hierarchy h24
ON (h24.parentid=coc.id AND h24.primarytype='contentWorksGroup' AND
h24.pos=0)
LEFT OUTER JOIN contentworksgroup cwg ON (cwg.id=h24.id);
On Thu, Jun 23, 2016 at 9:38 AM, talk-request@lists.collectionspace.org
wrote:
Send Talk mailing list submissions to
talk@lists.collectionspace.org
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
or, via email, send a message with subject or body 'help' to
talk-request@lists.collectionspace.org
You can reach the person managing the list at
talk-owner@lists.collectionspace.org
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Talk digest..."
Today's Topics:
1. Re: Searching for complete source code
@org.collectionspace.services.movement.MovementsCommon (Yousuf
Nejati)
Message: 1
Date: Thu, 23 Jun 2016 09:37:18 -0700
From: Yousuf Nejati yousuf.nejati@granitehorizon.com
To: "Christopher R. HOFFMAN" chris_h@berkeley.edu
Cc: Ray Lee rhlee@berkeley.edu, Jesse Martinez mjesse@gmail.com,
"talk@lists.collectionspace.org" talk@lists.collectionspace.org,
Peter Tucker peter.tucker@granitehorizon.com
Subject: Re: [Talk] Searching for complete source code
@org.collectionspace.services.movement.MovementsCommon
Message-ID:
<CABHa=0j1MM94xH=
tCjtwSafg5HmmeWr7grJ4CinTTeMgbGCPAQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Ray and Chris,
I'm still having a hard time understanding how, or better yet where,
exactly a collection object references a work authority? It doesn't seem to
be in relations_common, or is it?
"Instead a collection object will contain a field whose value is the
refname of a work.'-Ray
Ray, are you suggesting to join the relations_common ref column with the
collectionspace_core or works_common ref column, leaving me with a list of
works?
Chris, I've been doing this in the SELECT statement to extract out the
display name from the refname:
split_part(cscore.refname,'''',2) AS
works_s,
What could I provide you to make this easier for everyone?
Thanks,
-Yousuf
On Thu, Jun 23, 2016 at 7:47 AM, Christopher R. HOFFMAN <
chris_h@berkeley.edu> wrote:
Thanks, Ray, this is what I was wondering. I didn't know if the Fine Arts
schema had a Work Items procedure as well as a Works authority.
Here at Berkeley we are using the Works authority so we can just use the
refname, and in fact we just extract out the display name rather than
to the authority table.
Thanks,
Chris
Jun 22, 2016, at 8:54 PM, Ray Lee rhlee@berkeley.edu wrote:
Watch out, works are authority records (not procedural records), so
they're not related to collection objects in the way that procedural
records are related. There's no join through relations_common. Instead a
collection object will contain a field whose value is the refname of a
work. You can join to works_common or collectionspace_core through the
refname column, then join to other tables using the id column. The trick
that when you compare refnames, you should compare only the part that
not include the display name. I'm sure someone on the list has some
SQL for this.
Ray
On Wed, Jun 22, 2016 at 8:30 PM, Jesse Martinez mjesse@gmail.com
I've used the following SQL pattern in a few reports: (replace
groups_common and collectionobjects_common with their respective related
procedures)
FROM groups_common gc
JOIN hierarchy h1 ON (gc.id=h1.id)
JOIN relations_common rc1 ON (h1.name=rc1.subjectcsid)
JOIN hierarchy h2 ON (rc1.objectcsid=h2.name)
JOIN collectionobjects_common co ON (h2.id=co.id)
...
INNER JOIN misc ON (misc.id = co.id AND misc.lifecyclestate <>
INNER JOIN collectionspace_core core ON (core.id = co.id)
INNER JOIN collectionobjects_TENANT ctenant ON (co.id = ctenant.id)
Jesse
On Wed, Jun 22, 2016 at 11:12 PM, Yousuf Nejati <
yousuf.nejati@granitehorizon.com> wrote:
Hey,
Thanks for being so responsive, we really appreciate it.
I've discovered how difficult this is through speaking with Lowe, and
first hand. We are using the core schema.
Here is the broken SQL fragment, where coc is collectionobjects_common.
LEFT OUTER JOIN hierarchy h24 ON (h24.id=coc.id)
LEFT OUTER JOIN relations_common r2 ON (r2.subjectcsid=h24.name AND
r2.subjectdocumenttype='CollectionObject')
LEFT OUTER JOIN hierarchy h25 ON (h25.name=r2.objectcsid AND
r2.objectdocumenttype='Workitem')
LEFT OUTER JOIN works_common wc ON (h25.name=wc.id);
Any ideas?
-Y
On Wed, Jun 22, 2016 at 7:52 PM, Christopher R. HOFFMAN <
chris_h@berkeley.edu> wrote:
Hi Yousuf,
These queries are notoriously challenging given the underlying
structure of Nuxeo. Are you using the core scheme or the Fine Arts
extension, and have you customized how related works are represented?
I don't think we're using works in the standard way here at Berkeley
we probably can't give you the exact SQL fragment, but hopefully we
help with the overall pattern. I'll look at this a bit tomorrow if we
see more traffic on the list.
Regards,
Chris
On Jun 22, 2016, at 6:02 PM, Yousuf Nejati <
yousuf.nejati@granitehorizon.com> wrote:
Hi,
Thanks for your response.
I believe what we are really trying to understand is how the
CollectionSpace platform ui derives from the Postgres database to
things such as collection object and work relations, etc.
Does that make sense? We've deployed the UCB webapp and are currently
extending it to meet our needs. Implementing a custom query to index
has been quite frustrating, to say the least.
For example, we currently wish to obtain all of the Workitems related
to a CollectionObject. We are so very close.
-Yousuf
On Jun 22, 2016 5:54 PM, "Richard Millet" <richard.millet@lyrasis.org
Ray,
Well, I wish they were obsolete but we're not quite there yet. There
are still places in the code (Services layer) that rely on those
The "common" schemas/classes don't change (haven't changed?) so
encountered any problems.
-Richard
From: Ray Lee rhlee@berkeley.edu
Sent: Wednesday, June 22, 2016 5:41 PM
To: Richard Millet
Cc: Peter Tucker; talk@lists.collectionspace.org; Yousuf Nejati
Subject: Re: [Talk] Searching for complete source code
@org.collectionspace.services.movement.MovementsCommon
Richard,
I think those JAXB classes are obsolete now, aren't they? The .xsd
files for records are now generated from app layer config, but the
classes are not generated from those generated .xsd files. I think
still using old static .xsd files, which may not be up to date with
Peter,
MovementsCommon.java is a JAX-B class generated from the XML Schema
file movements-common.xsd. It is essentially just a way for
Sent: Wednesday, June 22, 2016 5:03 PM
To: talk@lists.collectionspace.org<mailto:
talk@lists.collectionspace.org>
Cc: Yousuf Nejati
Subject: [Talk] Searching for complete source code
@org.collectionspace.services.movement.MovementsCommon
Hi everyone,
We were curious about the code that is in MovementsCommon.java class
since we're doing some database queries that's related to movements
records and wanted to understand the process more. However currently
<%28916%29%20647-6350%20x730> | (866) 867-7126 (fax)
8153 Elk Grove Boulevard Suite 20
Elk Grove CA 95758-5965
Talk mailing list
Talk@lists.collectionspace.org
--
Yousuf Nejati
- Assistant Developer*
Granite Horizon
Web Design and Development | Enterprise Content Management | IT Consulting
yousuf.nejati@granitehorizon.com greg@granitehorizon.com
granitehorizon.com
(888) 354-6626 x786 <%28888%29%20354-6626%20x730> | (916) 647-6350 x786
<%28916%29%20647-6350%20x730> | (866) 867-7126 (fax)
8153 Elk Grove Boulevard Suite 20
Elk Grove CA 95758-5965
Hello all,
I believe we got it figured out, turns out the postgres fragment was much
simpler than we thought.
To obtain the work terms used by a collection object, do:
SELECT
split_part(cwg.contentwork,'''',2) AS contentworks_s
FROM collectionobjects_common coc
LEFT OUTER JOIN hierarchy h24
ON (h24.parentid=coc.id AND h24.primarytype='contentWorksGroup' AND
h24.pos=0)
LEFT OUTER JOIN contentworksgroup cwg ON (cwg.id=h24.id);
On Thu, Jun 23, 2016 at 9:38 AM, <talk-request@lists.collectionspace.org>
wrote:
> Send Talk mailing list submissions to
> talk@lists.collectionspace.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>
> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
>
> or, via email, send a message with subject or body 'help' to
> talk-request@lists.collectionspace.org
>
> You can reach the person managing the list at
> talk-owner@lists.collectionspace.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of Talk digest..."
>
>
> Today's Topics:
>
> 1. Re: Searching for complete source code
> @org.collectionspace.services.movement.MovementsCommon (Yousuf
> Nejati)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 23 Jun 2016 09:37:18 -0700
> From: Yousuf Nejati <yousuf.nejati@granitehorizon.com>
> To: "Christopher R. HOFFMAN" <chris_h@berkeley.edu>
> Cc: Ray Lee <rhlee@berkeley.edu>, Jesse Martinez <mjesse@gmail.com>,
> "talk@lists.collectionspace.org" <talk@lists.collectionspace.org>,
> Peter Tucker <peter.tucker@granitehorizon.com>
> Subject: Re: [Talk] Searching for complete source code
> @org.collectionspace.services.movement.MovementsCommon
> Message-ID:
> <CABHa=0j1MM94xH=
> tCjtwSafg5HmmeWr7grJ4CinTTeMgbGCPAQ@mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Ray and Chris,
>
> I'm still having a hard time understanding how, or better yet where,
> exactly a collection object references a work authority? It doesn't seem to
> be in relations_common, or is it?
>
> "Instead a collection object will contain a field whose value is the
> refname of a work.'-Ray
>
> Ray, are you suggesting to join the relations_common ref column with the
> collectionspace_core or works_common ref column, leaving me with a list of
> works?
>
> Chris, I've been doing this in the SELECT statement to extract out the
> display name from the refname:
>
> split_part(cscore.refname,'''',2) AS
> works_s,
>
> What could I provide you to make this easier for everyone?
>
> Thanks,
>
> -Yousuf
>
> On Thu, Jun 23, 2016 at 7:47 AM, Christopher R. HOFFMAN <
> chris_h@berkeley.edu> wrote:
>
> > Thanks, Ray, this is what I was wondering. I didn't know if the Fine Arts
> > schema had a Work Items procedure as well as a Works authority.
> >
> > Here at Berkeley we are using the Works authority so we can just use the
> > refname, and in fact we just extract out the display name rather than
> join
> > to the authority table.
> >
> > Thanks,
> > Chris
> >
> > Jun 22, 2016, at 8:54 PM, Ray Lee <rhlee@berkeley.edu> wrote:
> >
> > Watch out, works are authority records (not procedural records), so
> > they're not related to collection objects in the way that procedural
> > records are related. There's no join through relations_common. Instead a
> > collection object will contain a field whose value is the refname of a
> > work. You can join to works_common or collectionspace_core through the
> > refname column, then join to other tables using the id column. The trick
> is
> > that when you compare refnames, you should compare only the part that
> does
> > not include the display name. I'm sure someone on the list has some
> sample
> > SQL for this.
> >
> > Ray
> >
> >
> > On Wed, Jun 22, 2016 at 8:30 PM, Jesse Martinez <mjesse@gmail.com>
> wrote:
> >
> >> I've used the following SQL pattern in a few reports: (replace
> >> groups_common and collectionobjects_common with their respective related
> >> procedures)
> >>
> >> FROM groups_common gc
> >>
> >> JOIN hierarchy h1 ON (gc.id=h1.id)
> >> JOIN relations_common rc1 ON (h1.name=rc1.subjectcsid)
> >>
> >> JOIN hierarchy h2 ON (rc1.objectcsid=h2.name)
> >> JOIN collectionobjects_common co ON (h2.id=co.id)
> >>
> >> ...
> >>
> >> INNER JOIN misc ON (misc.id = co.id AND misc.lifecyclestate <>
> 'deleted')
> >> INNER JOIN collectionspace_core core ON (core.id = co.id)
> >> INNER JOIN collectionobjects_TENANT ctenant ON (co.id = ctenant.id)
> >>
> >>
> >> Jesse
> >>
> >> On Wed, Jun 22, 2016 at 11:12 PM, Yousuf Nejati <
> >> yousuf.nejati@granitehorizon.com> wrote:
> >>
> >>> Hey,
> >>>
> >>> Thanks for being so responsive, we really appreciate it.
> >>>
> >>> I've discovered how difficult this is through speaking with Lowe, and
> >>> first hand. We are using the core schema.
> >>>
> >>> Here is the broken SQL fragment, where coc is collectionobjects_common.
> >>>
> >>> LEFT OUTER JOIN hierarchy h24 ON (h24.id=coc.id)
> >>> LEFT OUTER JOIN relations_common r2 ON (r2.subjectcsid=h24.name AND
> >>> r2.subjectdocumenttype='CollectionObject')
> >>> LEFT OUTER JOIN hierarchy h25 ON (h25.name=r2.objectcsid AND
> >>> r2.objectdocumenttype='Workitem')
> >>> LEFT OUTER JOIN works_common wc ON (h25.name=wc.id);
> >>>
> >>> Any ideas?
> >>>
> >>> -Y
> >>>
> >>> On Wed, Jun 22, 2016 at 7:52 PM, Christopher R. HOFFMAN <
> >>> chris_h@berkeley.edu> wrote:
> >>>
> >>>> Hi Yousuf,
> >>>>
> >>>> These queries are notoriously challenging given the underlying
> >>>> structure of Nuxeo. Are you using the core scheme or the Fine Arts
> >>>> extension, and have you customized how related works are represented?
> >>>>
> >>>> I don't think we're using works in the standard way here at Berkeley
> so
> >>>> we probably can't give you the exact SQL fragment, but hopefully we
> can
> >>>> help with the overall pattern. I'll look at this a bit tomorrow if we
> don't
> >>>> see more traffic on the list.
> >>>>
> >>>> Regards,
> >>>> Chris
> >>>>
> >>>>
> >>>> On Jun 22, 2016, at 6:02 PM, Yousuf Nejati <
> >>>> yousuf.nejati@granitehorizon.com> wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> Thanks for your response.
> >>>>
> >>>> I believe what we are really trying to understand is how the
> >>>> CollectionSpace platform ui derives from the Postgres database to
> provide
> >>>> things such as collection object and work relations, etc.
> >>>>
> >>>> Does that make sense? We've deployed the UCB webapp and are currently
> >>>> extending it to meet our needs. Implementing a custom query to index
> Solr
> >>>> has been quite frustrating, to say the least.
> >>>>
> >>>> For example, we currently wish to obtain all of the Workitems related
> >>>> to a CollectionObject. We are so very close.
> >>>>
> >>>> -Yousuf
> >>>> On Jun 22, 2016 5:54 PM, "Richard Millet" <richard.millet@lyrasis.org
> >
> >>>> wrote:
> >>>>
> >>>>> Ray,
> >>>>>
> >>>>> Well, I wish they were obsolete but we're not quite there yet. There
> >>>>> are still places in the code (Services layer) that rely on those
> classes.
> >>>>> The "common" schemas/classes don't change (haven't changed?) so
> we've not
> >>>>> encountered any problems.
> >>>>>
> >>>>> -Richard
> >>>>>
> >>>>> ________________________________________
> >>>>> From: Ray Lee <rhlee@berkeley.edu>
> >>>>> Sent: Wednesday, June 22, 2016 5:41 PM
> >>>>> To: Richard Millet
> >>>>> Cc: Peter Tucker; talk@lists.collectionspace.org; Yousuf Nejati
> >>>>> Subject: Re: [Talk] Searching for complete source code
> >>>>> @org.collectionspace.services.movement.MovementsCommon
> >>>>>
> >>>>> Richard,
> >>>>> I think those JAXB classes are obsolete now, aren't they? The .xsd
> >>>>> files for records are now generated from app layer config, but the
> JAXB
> >>>>> classes are not generated from those generated .xsd files. I think
> they're
> >>>>> still using old static .xsd files, which may not be up to date with
> what's
> >>>>> defined in the app layer now.
> >>>>>
> >>>>> Ray
> >>>>>
> >>>>>
> >>>>> On Wed, Jun 22, 2016 at 5:28 PM, Richard Millet <
> >>>>> richard.millet@lyrasis.org<mailto:richard.millet@lyrasis.org>>
> wrote:
> >>>>> Peter,
> >>>>>
> >>>>> MovementsCommon.java is a JAX-B class generated from the XML Schema
> >>>>> file movements-common.xsd. It is essentially just a way for
> converting XML
> >>>>> to Java class instances and vice versa -see
> >>>>> https://en.wikipedia.org/wiki/Java_Architecture_for_XML_Binding for
> >>>>> more details.
> >>>>>
> >>>>> -Richard
> >>>>>
> >>>>> ________________________________________
> >>>>> From: Talk <talk-bounces@lists.collectionspace.org<mailto:
> >>>>> talk-bounces@lists.collectionspace.org>> on behalf of Peter Tucker <
> >>>>> peter.tucker@granitehorizon.com<mailto:
> peter.tucker@granitehorizon.com
> >>>>> >>
> >>>>> Sent: Wednesday, June 22, 2016 5:03 PM
> >>>>> To: talk@lists.collectionspace.org<mailto:
> >>>>> talk@lists.collectionspace.org>
> >>>>> Cc: Yousuf Nejati
> >>>>> Subject: [Talk] Searching for complete source code
> >>>>> @org.collectionspace.services.movement.MovementsCommon
> >>>>>
> >>>>> Hi everyone,
> >>>>>
> >>>>> We were curious about the code that is in MovementsCommon.java class
> ,
> >>>>> since we're doing some database queries that's related to movements
> >>>>> records and wanted to understand the process more. However currently
> in
> >>>>> the CS github, we can't find the source code of the specific class.
> >>>>>
> >>>>> Thank you,
> >>>>> Peter
> >>>>>
> >>>>> _______________________________________________
> >>>>> Talk mailing list
> >>>>> Talk@lists.collectionspace.org<mailto:Talk@lists.collectionspace.org
> >
> >>>>>
> >>>>>
> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
> >>>>>
> >>>>> _______________________________________________
> >>>>> Talk mailing list
> >>>>> Talk@lists.collectionspace.org<mailto: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
> >>>>
> >>>>
> >>>
> >>>
> >>> --
> >>> Yousuf Nejati
> >>> * Assistant Developer*
> >>> Granite Horizon
> >>> Web Design and Development | Enterprise Content Management | IT
> >>> Consulting
> >>>
> >>> yousuf.nejati@granitehorizon.com <greg@granitehorizon.com>
> >>> granitehorizon.com
> >>> (888) 354-6626 x786 <%28888%29%20354-6626%20x730> | (916) 647-6350
> x786
> >>> <%28916%29%20647-6350%20x730> | (866) 867-7126 (fax)
> >>> 8153 Elk Grove Boulevard Suite 20
> >>> Elk Grove CA 95758-5965
> >>>
> >>> _______________________________________________
> >>> 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
> >
> >
>
>
> --
> Yousuf Nejati
> * Assistant Developer*
> Granite Horizon
> Web Design and Development | Enterprise Content Management | IT Consulting
>
> yousuf.nejati@granitehorizon.com <greg@granitehorizon.com>
> granitehorizon.com
> (888) 354-6626 x786 <%28888%29%20354-6626%20x730> | (916) 647-6350 x786
> <%28916%29%20647-6350%20x730> | (866) 867-7126 (fax)
> 8153 Elk Grove Boulevard Suite 20
> Elk Grove CA 95758-5965
>
CH
Chris Hoffman
Thu, Jun 23, 2016 7:54 PM
Hi Yousuf, glad to hear you got that working! It even has the part to select only the first record (pos=0).
Chris
On Jun 23, 2016, at 11:11 AM, Yousuf Nejati yousuf.nejati@granitehorizon.com wrote:
Hello all,
I believe we got it figured out, turns out the postgres fragment was much simpler than we thought.
To obtain the work terms used by a collection object, do:
SELECT
split_part(cwg.contentwork,'''',2) AS contentworks_s
FROM collectionobjects_common coc
LEFT OUTER JOIN hierarchy h24
ON (h24.parentid=coc.id http://coc.id/ AND h24.primarytype='contentWorksGroup' AND h24.pos=0)
LEFT OUTER JOIN contentworksgroup cwg ON (cwg.id http://cwg.id/=h24.id http://h24.id/);
On Thu, Jun 23, 2016 at 9:38 AM, <talk-request@lists.collectionspace.org mailto:talk-request@lists.collectionspace.org> wrote:
Send Talk mailing list submissions to
talk@lists.collectionspace.org mailto:talk@lists.collectionspace.org
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
or, via email, send a message with subject or body 'help' to
talk-request@lists.collectionspace.org mailto:talk-request@lists.collectionspace.org
You can reach the person managing the list at
talk-owner@lists.collectionspace.org mailto:talk-owner@lists.collectionspace.org
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Talk digest..."
Today's Topics:
1. Re: Searching for complete source code
@org.collectionspace.services.movement.MovementsCommon (Yousuf Nejati)
Message: 1
Date: Thu, 23 Jun 2016 09:37:18 -0700
From: Yousuf Nejati <yousuf.nejati@granitehorizon.com mailto:yousuf.nejati@granitehorizon.com>
To: "Christopher R. HOFFMAN" <chris_h@berkeley.edu mailto:chris_h@berkeley.edu>
Cc: Ray Lee <rhlee@berkeley.edu mailto:rhlee@berkeley.edu>, Jesse Martinez <mjesse@gmail.com mailto:mjesse@gmail.com>,
"talk@lists.collectionspace.org mailto:talk@lists.collectionspace.org" <talk@lists.collectionspace.org mailto:talk@lists.collectionspace.org>,
Peter Tucker <peter.tucker@granitehorizon.com mailto:peter.tucker@granitehorizon.com>
Subject: Re: [Talk] Searching for complete source code
@org.collectionspace.services.movement.MovementsCommon
Message-ID:
<CABHa=0j1MM94xH=tCjtwSafg5HmmeWr7grJ4CinTTeMgbGCPAQ@mail.gmail.com mailto:tCjtwSafg5HmmeWr7grJ4CinTTeMgbGCPAQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Ray and Chris,
I'm still having a hard time understanding how, or better yet where,
exactly a collection object references a work authority? It doesn't seem to
be in relations_common, or is it?
"Instead a collection object will contain a field whose value is the
refname of a work.'-Ray
Ray, are you suggesting to join the relations_common ref column with the
collectionspace_core or works_common ref column, leaving me with a list of
works?
Chris, I've been doing this in the SELECT statement to extract out the
display name from the refname:
split_part(cscore.refname,'''',2) AS
works_s,
What could I provide you to make this easier for everyone?
Thanks,
-Yousuf
On Thu, Jun 23, 2016 at 7:47 AM, Christopher R. HOFFMAN <
chris_h@berkeley.edu mailto:chris_h@berkeley.edu> wrote:
Thanks, Ray, this is what I was wondering. I didn't know if the Fine Arts
schema had a Work Items procedure as well as a Works authority.
Here at Berkeley we are using the Works authority so we can just use the
refname, and in fact we just extract out the display name rather than join
to the authority table.
Thanks,
Chris
Jun 22, 2016, at 8:54 PM, Ray Lee <rhlee@berkeley.edu mailto:rhlee@berkeley.edu> wrote:
Watch out, works are authority records (not procedural records), so
they're not related to collection objects in the way that procedural
records are related. There's no join through relations_common. Instead a
collection object will contain a field whose value is the refname of a
work. You can join to works_common or collectionspace_core through the
refname column, then join to other tables using the id column. The trick is
that when you compare refnames, you should compare only the part that does
not include the display name. I'm sure someone on the list has some sample
SQL for this.
Ray
On Wed, Jun 22, 2016 at 8:30 PM, Jesse Martinez <mjesse@gmail.com mailto:mjesse@gmail.com> wrote:
Hey,
Thanks for being so responsive, we really appreciate it.
I've discovered how difficult this is through speaking with Lowe, and
first hand. We are using the core schema.
Here is the broken SQL fragment, where coc is collectionobjects_common.
LEFT OUTER JOIN hierarchy h24 ON (h24.id http://h24.id/=coc.id http://coc.id/)
LEFT OUTER JOIN relations_common r2 ON (r2.subjectcsid=h24.name http://h24.name/ AND
r2.subjectdocumenttype='CollectionObject')
LEFT OUTER JOIN hierarchy h25 ON (h25.name http://h25.name/=r2.objectcsid AND
r2.objectdocumenttype='Workitem')
LEFT OUTER JOIN works_common wc ON (h25.name http://h25.name/=wc.id http://wc.id/);
Any ideas?
-Y
On Wed, Jun 22, 2016 at 7:52 PM, Christopher R. HOFFMAN <
chris_h@berkeley.edu mailto:chris_h@berkeley.edu> wrote:
Hi Yousuf,
These queries are notoriously challenging given the underlying
structure of Nuxeo. Are you using the core scheme or the Fine Arts
extension, and have you customized how related works are represented?
I don't think we're using works in the standard way here at Berkeley so
we probably can't give you the exact SQL fragment, but hopefully we can
help with the overall pattern. I'll look at this a bit tomorrow if we don't
see more traffic on the list.
Regards,
Chris
On Jun 22, 2016, at 6:02 PM, Yousuf Nejati <
yousuf.nejati@granitehorizon.com mailto:yousuf.nejati@granitehorizon.com> wrote:
Hi,
Thanks for your response.
I believe what we are really trying to understand is how the
CollectionSpace platform ui derives from the Postgres database to provide
things such as collection object and work relations, etc.
Does that make sense? We've deployed the UCB webapp and are currently
extending it to meet our needs. Implementing a custom query to index Solr
has been quite frustrating, to say the least.
For example, we currently wish to obtain all of the Workitems related
to a CollectionObject. We are so very close.
-Yousuf
On Jun 22, 2016 5:54 PM, "Richard Millet" <richard.millet@lyrasis.org mailto:richard.millet@lyrasis.org>
wrote:
Ray,
Well, I wish they were obsolete but we're not quite there yet. There
are still places in the code (Services layer) that rely on those classes.
The "common" schemas/classes don't change (haven't changed?) so we've not
encountered any problems.
-Richard
From: Ray Lee <rhlee@berkeley.edu mailto:rhlee@berkeley.edu>
Sent: Wednesday, June 22, 2016 5:41 PM
To: Richard Millet
Cc: Peter Tucker; talk@lists.collectionspace.org mailto:talk@lists.collectionspace.org; Yousuf Nejati
Subject: Re: [Talk] Searching for complete source code
@org.collectionspace.services.movement.MovementsCommon
Richard,
I think those JAXB classes are obsolete now, aren't they? The .xsd
files for records are now generated from app layer config, but the JAXB
classes are not generated from those generated .xsd files. I think they're
still using old static .xsd files, which may not be up to date with what's
defined in the app layer now.
Ray
On Wed, Jun 22, 2016 at 5:28 PM, Richard Millet <
richard.millet@lyrasis.org mailto:richard.millet@lyrasis.org<mailto:richard.millet@lyrasis.org mailto:richard.millet@lyrasis.org>> wrote:
Peter,
MovementsCommon.java is a JAX-B class generated from the XML Schema
file movements-common.xsd. It is essentially just a way for converting XML
to Java class instances and vice versa -see
https://en.wikipedia.org/wiki/Java_Architecture_for_XML_Binding https://en.wikipedia.org/wiki/Java_Architecture_for_XML_Binding for
more details.
-Richard
From: Talk <talk-bounces@lists.collectionspace.org mailto:talk-bounces@lists.collectionspace.org<mailto:
talk-bounces@lists.collectionspace.org mailto:talk-bounces@lists.collectionspace.org>> on behalf of Peter Tucker <
peter.tucker@granitehorizon.com mailto:peter.tucker@granitehorizon.com<mailto:peter.tucker@granitehorizon.com mailto:peter.tucker@granitehorizon.com
Hi Yousuf, glad to hear you got that working! It even has the part to select only the first record (pos=0).
Chris
> On Jun 23, 2016, at 11:11 AM, Yousuf Nejati <yousuf.nejati@granitehorizon.com> wrote:
>
> Hello all,
>
> I believe we got it figured out, turns out the postgres fragment was much simpler than we thought.
>
> To obtain the work terms used by a collection object, do:
>
> SELECT
>
> split_part(cwg.contentwork,'''',2) AS contentworks_s
>
> FROM collectionobjects_common coc
>
> LEFT OUTER JOIN hierarchy h24
> ON (h24.parentid=coc.id <http://coc.id/> AND h24.primarytype='contentWorksGroup' AND h24.pos=0)
> LEFT OUTER JOIN contentworksgroup cwg ON (cwg.id <http://cwg.id/>=h24.id <http://h24.id/>);
>
>
>
> On Thu, Jun 23, 2016 at 9:38 AM, <talk-request@lists.collectionspace.org <mailto:talk-request@lists.collectionspace.org>> wrote:
> Send Talk mailing list submissions to
> talk@lists.collectionspace.org <mailto:talk@lists.collectionspace.org>
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org <http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org>
>
> or, via email, send a message with subject or body 'help' to
> talk-request@lists.collectionspace.org <mailto:talk-request@lists.collectionspace.org>
>
> You can reach the person managing the list at
> talk-owner@lists.collectionspace.org <mailto:talk-owner@lists.collectionspace.org>
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of Talk digest..."
>
>
> Today's Topics:
>
> 1. Re: Searching for complete source code
> @org.collectionspace.services.movement.MovementsCommon (Yousuf Nejati)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 23 Jun 2016 09:37:18 -0700
> From: Yousuf Nejati <yousuf.nejati@granitehorizon.com <mailto:yousuf.nejati@granitehorizon.com>>
> To: "Christopher R. HOFFMAN" <chris_h@berkeley.edu <mailto:chris_h@berkeley.edu>>
> Cc: Ray Lee <rhlee@berkeley.edu <mailto:rhlee@berkeley.edu>>, Jesse Martinez <mjesse@gmail.com <mailto:mjesse@gmail.com>>,
> "talk@lists.collectionspace.org <mailto:talk@lists.collectionspace.org>" <talk@lists.collectionspace.org <mailto:talk@lists.collectionspace.org>>,
> Peter Tucker <peter.tucker@granitehorizon.com <mailto:peter.tucker@granitehorizon.com>>
> Subject: Re: [Talk] Searching for complete source code
> @org.collectionspace.services.movement.MovementsCommon
> Message-ID:
> <CABHa=0j1MM94xH=tCjtwSafg5HmmeWr7grJ4CinTTeMgbGCPAQ@mail.gmail.com <mailto:tCjtwSafg5HmmeWr7grJ4CinTTeMgbGCPAQ@mail.gmail.com>>
> Content-Type: text/plain; charset="utf-8"
>
> Ray and Chris,
>
> I'm still having a hard time understanding how, or better yet where,
> exactly a collection object references a work authority? It doesn't seem to
> be in relations_common, or is it?
>
> "Instead a collection object will contain a field whose value is the
> refname of a work.'-Ray
>
> Ray, are you suggesting to join the relations_common ref column with the
> collectionspace_core or works_common ref column, leaving me with a list of
> works?
>
> Chris, I've been doing this in the SELECT statement to extract out the
> display name from the refname:
>
> split_part(cscore.refname,'''',2) AS
> works_s,
>
> What could I provide you to make this easier for everyone?
>
> Thanks,
>
> -Yousuf
>
> On Thu, Jun 23, 2016 at 7:47 AM, Christopher R. HOFFMAN <
> chris_h@berkeley.edu <mailto:chris_h@berkeley.edu>> wrote:
>
> > Thanks, Ray, this is what I was wondering. I didn't know if the Fine Arts
> > schema had a Work Items procedure as well as a Works authority.
> >
> > Here at Berkeley we are using the Works authority so we can just use the
> > refname, and in fact we just extract out the display name rather than join
> > to the authority table.
> >
> > Thanks,
> > Chris
> >
> > Jun 22, 2016, at 8:54 PM, Ray Lee <rhlee@berkeley.edu <mailto:rhlee@berkeley.edu>> wrote:
> >
> > Watch out, works are authority records (not procedural records), so
> > they're not related to collection objects in the way that procedural
> > records are related. There's no join through relations_common. Instead a
> > collection object will contain a field whose value is the refname of a
> > work. You can join to works_common or collectionspace_core through the
> > refname column, then join to other tables using the id column. The trick is
> > that when you compare refnames, you should compare only the part that does
> > not include the display name. I'm sure someone on the list has some sample
> > SQL for this.
> >
> > Ray
> >
> >
> > On Wed, Jun 22, 2016 at 8:30 PM, Jesse Martinez <mjesse@gmail.com <mailto:mjesse@gmail.com>> wrote:
> >
> >> I've used the following SQL pattern in a few reports: (replace
> >> groups_common and collectionobjects_common with their respective related
> >> procedures)
> >>
> >> FROM groups_common gc
> >>
> >> JOIN hierarchy h1 ON (gc.id <http://gc.id/>=h1.id <http://h1.id/>)
> >> JOIN relations_common rc1 ON (h1.name <http://h1.name/>=rc1.subjectcsid)
> >>
> >> JOIN hierarchy h2 ON (rc1.objectcsid=h2.name <http://h2.name/>)
> >> JOIN collectionobjects_common co ON (h2.id <http://h2.id/>=co.id <http://co.id/>)
> >>
> >> ...
> >>
> >> INNER JOIN misc ON (misc.id <http://misc.id/> = co.id <http://co.id/> AND misc.lifecyclestate <> 'deleted')
> >> INNER JOIN collectionspace_core core ON (core.id <http://core.id/> = co.id <http://co.id/>)
> >> INNER JOIN collectionobjects_TENANT ctenant ON (co.id <http://co.id/> = ctenant.id <http://ctenant.id/>)
> >>
> >>
> >> Jesse
> >>
> >> On Wed, Jun 22, 2016 at 11:12 PM, Yousuf Nejati <
> >> yousuf.nejati@granitehorizon.com <mailto:yousuf.nejati@granitehorizon.com>> wrote:
> >>
> >>> Hey,
> >>>
> >>> Thanks for being so responsive, we really appreciate it.
> >>>
> >>> I've discovered how difficult this is through speaking with Lowe, and
> >>> first hand. We are using the core schema.
> >>>
> >>> Here is the broken SQL fragment, where coc is collectionobjects_common.
> >>>
> >>> LEFT OUTER JOIN hierarchy h24 ON (h24.id <http://h24.id/>=coc.id <http://coc.id/>)
> >>> LEFT OUTER JOIN relations_common r2 ON (r2.subjectcsid=h24.name <http://h24.name/> AND
> >>> r2.subjectdocumenttype='CollectionObject')
> >>> LEFT OUTER JOIN hierarchy h25 ON (h25.name <http://h25.name/>=r2.objectcsid AND
> >>> r2.objectdocumenttype='Workitem')
> >>> LEFT OUTER JOIN works_common wc ON (h25.name <http://h25.name/>=wc.id <http://wc.id/>);
> >>>
> >>> Any ideas?
> >>>
> >>> -Y
> >>>
> >>> On Wed, Jun 22, 2016 at 7:52 PM, Christopher R. HOFFMAN <
> >>> chris_h@berkeley.edu <mailto:chris_h@berkeley.edu>> wrote:
> >>>
> >>>> Hi Yousuf,
> >>>>
> >>>> These queries are notoriously challenging given the underlying
> >>>> structure of Nuxeo. Are you using the core scheme or the Fine Arts
> >>>> extension, and have you customized how related works are represented?
> >>>>
> >>>> I don't think we're using works in the standard way here at Berkeley so
> >>>> we probably can't give you the exact SQL fragment, but hopefully we can
> >>>> help with the overall pattern. I'll look at this a bit tomorrow if we don't
> >>>> see more traffic on the list.
> >>>>
> >>>> Regards,
> >>>> Chris
> >>>>
> >>>>
> >>>> On Jun 22, 2016, at 6:02 PM, Yousuf Nejati <
> >>>> yousuf.nejati@granitehorizon.com <mailto:yousuf.nejati@granitehorizon.com>> wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> Thanks for your response.
> >>>>
> >>>> I believe what we are really trying to understand is how the
> >>>> CollectionSpace platform ui derives from the Postgres database to provide
> >>>> things such as collection object and work relations, etc.
> >>>>
> >>>> Does that make sense? We've deployed the UCB webapp and are currently
> >>>> extending it to meet our needs. Implementing a custom query to index Solr
> >>>> has been quite frustrating, to say the least.
> >>>>
> >>>> For example, we currently wish to obtain all of the Workitems related
> >>>> to a CollectionObject. We are so very close.
> >>>>
> >>>> -Yousuf
> >>>> On Jun 22, 2016 5:54 PM, "Richard Millet" <richard.millet@lyrasis.org <mailto:richard.millet@lyrasis.org>>
> >>>> wrote:
> >>>>
> >>>>> Ray,
> >>>>>
> >>>>> Well, I wish they were obsolete but we're not quite there yet. There
> >>>>> are still places in the code (Services layer) that rely on those classes.
> >>>>> The "common" schemas/classes don't change (haven't changed?) so we've not
> >>>>> encountered any problems.
> >>>>>
> >>>>> -Richard
> >>>>>
> >>>>> ________________________________________
> >>>>> From: Ray Lee <rhlee@berkeley.edu <mailto:rhlee@berkeley.edu>>
> >>>>> Sent: Wednesday, June 22, 2016 5:41 PM
> >>>>> To: Richard Millet
> >>>>> Cc: Peter Tucker; talk@lists.collectionspace.org <mailto:talk@lists.collectionspace.org>; Yousuf Nejati
> >>>>> Subject: Re: [Talk] Searching for complete source code
> >>>>> @org.collectionspace.services.movement.MovementsCommon
> >>>>>
> >>>>> Richard,
> >>>>> I think those JAXB classes are obsolete now, aren't they? The .xsd
> >>>>> files for records are now generated from app layer config, but the JAXB
> >>>>> classes are not generated from those generated .xsd files. I think they're
> >>>>> still using old static .xsd files, which may not be up to date with what's
> >>>>> defined in the app layer now.
> >>>>>
> >>>>> Ray
> >>>>>
> >>>>>
> >>>>> On Wed, Jun 22, 2016 at 5:28 PM, Richard Millet <
> >>>>> richard.millet@lyrasis.org <mailto:richard.millet@lyrasis.org><mailto:richard.millet@lyrasis.org <mailto:richard.millet@lyrasis.org>>> wrote:
> >>>>> Peter,
> >>>>>
> >>>>> MovementsCommon.java is a JAX-B class generated from the XML Schema
> >>>>> file movements-common.xsd. It is essentially just a way for converting XML
> >>>>> to Java class instances and vice versa -see
> >>>>> https://en.wikipedia.org/wiki/Java_Architecture_for_XML_Binding <https://en.wikipedia.org/wiki/Java_Architecture_for_XML_Binding> for
> >>>>> more details.
> >>>>>
> >>>>> -Richard
> >>>>>
> >>>>> ________________________________________
> >>>>> From: Talk <talk-bounces@lists.collectionspace.org <mailto:talk-bounces@lists.collectionspace.org><mailto:
> >>>>> talk-bounces@lists.collectionspace.org <mailto:talk-bounces@lists.collectionspace.org>>> on behalf of Peter Tucker <
> >>>>> peter.tucker@granitehorizon.com <mailto:peter.tucker@granitehorizon.com><mailto:peter.tucker@granitehorizon.com <mailto:peter.tucker@granitehorizon.com>
> >>>>> >>
> >>>>> Sent: Wednesday, June 22, 2016 5:03 PM
> >>>>> To: talk@lists.collectionspace.org <mailto:talk@lists.collectionspace.org><mailto:
> >>>>> talk@lists.collectionspace.org <mailto:talk@lists.collectionspace.org>>
> >>>>> Cc: Yousuf Nejati
> >>>>> Subject: [Talk] Searching for complete source code
> >>>>> @org.collectionspace.services.movement.MovementsCommon
> >>>>>
> >>>>> Hi everyone,
> >>>>>
> >>>>> We were curious about the code that is in MovementsCommon.java class ,
> >>>>> since we're doing some database queries that's related to movements
> >>>>> records and wanted to understand the process more. However currently in
> >>>>> the CS github, we can't find the source code of the specific class.
> >>>>>
> >>>>> Thank you,
> >>>>> Peter
> >>>>>
> >>>>> _______________________________________________
> >>>>> Talk mailing list
> >>>>> Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org><mailto:Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org>>
> >>>>>
> >>>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org <http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org>
> >>>>>
> >>>>> _______________________________________________
> >>>>> Talk mailing list
> >>>>> Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org><mailto:Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org>>
> >>>>>
> >>>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org <http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org>
> >>>>>
> >>>>> _______________________________________________
> >>>> Talk mailing list
> >>>> Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org>
> >>>>
> >>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org <http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org>
> >>>>
> >>>>
> >>>
> >>>
> >>> --
> >>> Yousuf Nejati
> >>> * Assistant Developer*
> >>> Granite Horizon
> >>> Web Design and Development | Enterprise Content Management | IT
> >>> Consulting
> >>>
> >>> yousuf.nejati@granitehorizon.com <mailto:yousuf.nejati@granitehorizon.com> <greg@granitehorizon.com <mailto:greg@granitehorizon.com>>
> >>> granitehorizon.com <http://granitehorizon.com/>
> >>> (888) 354-6626 x786 <tel:%28888%29%20354-6626%20x786> <%28888%29%20354-6626%20x730> | (916) 647-6350 x786 <tel:%28916%29%20647-6350%20x786>
> >>> <%28916%29%20647-6350%20x730> | (866) 867-7126 <tel:%28866%29%20867-7126> (fax)
> >>> 8153 Elk Grove Boulevard Suite 20
> >>> Elk Grove CA 95758-5965
> >>>
> >>> _______________________________________________
> >>> Talk mailing list
> >>> Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org>
> >>>
> >>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org <http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org>
> >>>
> >>>
> >>
> >> _______________________________________________
> >> Talk mailing list
> >> Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org>
> >>
> >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org <http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org>
> >>
> >>
> > _______________________________________________
> > Talk mailing list
> > Talk@lists.collectionspace.org <mailto:Talk@lists.collectionspace.org>
> >
> > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org <http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org>
> >
> >
>
>
> --
> Yousuf Nejati
> * Assistant Developer*
> Granite Horizon
> Web Design and Development | Enterprise Content Management | IT Consulting
>
> yousuf.nejati@granitehorizon.com <mailto:yousuf.nejati@granitehorizon.com> <greg@granitehorizon.com <mailto:greg@granitehorizon.com>>
> granitehorizon.com <http://granitehorizon.com/>
> (888) 354-6626 x786 <tel:%28888%29%20354-6626%20x786> <%28888%29%20354-6626%20x730> | (916) 647-6350 x786 <tel:%28916%29%20647-6350%20x786>
> <%28916%29%20647-6350%20x730> | (866) 867-7126 <tel:%28866%29%20867-7126> (fax)
> 8153 Elk Grove Boulevard Suite 20
> Elk Grove CA 95758-5965
>