talk@lists.collectionspace.org
WE HAVE SUNSET THIS LISTSERV - Join us at collectionspace@lyrasislists.org
View all threadsRe: [Talk] what table stores catalogue items and best practices for images in reports
Here are some sample scripts to help you understand how things are
generated, brought to you by the UC Berkeley Django Web Apps:
https://github.com/cspace-deployment/Tools/tree/master/datasources
For example (derived from the default Django Web App deployment
@ https://github.com/cspace-deployment/cspace_django_project/blob/master/solr/core.public.sql
https://github.com/cspace-deployment/cspace_django_project/blob/master/solr/core.public.sql
):
-- data extract to provision data-driven elements of the the core public
website
SELECT
-- the first field must be the unique id field and it must be called "id"
h1.name AS id,
h1.name AS
csid_s,
regexp_replace(ong.objectname, '^.)''(.)''$', '\1') AS
objectname_s,
coc.objectnumber AS
objectnumber_s,
coc.numberofobjects AS
numberofobjects_s,
coc.computedcurrentlocation AS
computedcurrentlocationrefname_s,
regexp_replace(coc.computedcurrentlocation, '^.)''(.)''$', '\1') AS
currentlocation_s,
regexp_replace(coc.recordstatus, '^.)''(.)''$', '\1') AS
recordstatus_s,
regexp_replace(coc.physicaldescription, E'[\t\n\r]+', ' ', 'g') AS
physicaldescription_s,
regexp_replace(coc.contentdescription, E'[\t\n\r]+', ' ', 'g') AS
contentdescription_s,
regexp_replace(coc.contentnote, E'[\t\n\r]+', ' ', 'g') AS
contentnote_s,
regexp_replace(coc.fieldcollectionplace, '^.)''(.)''$', '\1') AS
fieldcollectionplace_s,
regexp_replace(coc.collection, '^.)''(.)''$', '\1') AS
collection_s,
sdg.datedisplaydate AS
datemade_s,
coc.physicaldescription AS
materials_s,
replace(mp.dimensionsummary, '-', ' ') AS
measurement_s,
core.updatedat AS
updatedat_s
FROM collectionobjects_common coc
JOIN hierarchy h1 ON (h1.id = coc.id)
JOIN misc ON (coc.id = misc.id AND misc.lifecyclestate <> 'deleted')
LEFT OUTER JOIN hierarchy h2
ON (coc.id = h2.parentid AND h2.name =
'collectionobjects_common:objectNameList' AND h2.pos = 0)
LEFT OUTER JOIN objectnamegroup ong ON (ong.id = h2.id)
INNER JOIN collectionspace_core core ON coc.id = core.id
LEFT OUTER JOIN hierarchy h3
ON (h3.parentid = coc.id AND h3.name =
'collectionobjects_common:objectProductionDateGroupList' AND h3.pos = 0)
LEFT OUTER JOIN structuredDateGroup sdg ON (h3.id = sdg.id)
LEFT OUTER JOIN hierarchy h7
ON (h7.parentid = coc.id AND h7.name =
'collectionobjects_common:measuredPartGroupList' AND h7.pos = 0)
LEFT OUTER JOIN measuredpartgroup mp
ON (h7.id = mp.id)
LEFT OUTER JOIN hierarchy h14
ON (h14.parentid = coc.id AND h14.name =
'collectionobjects_common:objectProductionPlaceGroupList' AND h14.pos = 0)
;
On Thu, Sep 1, 2016 at 9:00 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. what table stores catalogue items and best practices for
images in reports (toolhater)
2. Re: what table stores catalogue items and best practices for
images in reports (Aron Roberts)
Message: 1
Date: Wed, 31 Aug 2016 23:11:57 +0000 (UTC)
From: toolhater toolhater@yahoo.com
To: CollectionSpace Talk List talk@lists.collectionspace.org
Subject: [Talk] what table stores catalogue items and best practices
for images in reports
Message-ID: 1125413578.2970968.1472685117929@mail.yahoo.com
Content-Type: text/plain; charset="utf-8"
Guys,
I'm trying to find out where everything i see on the front end goes in the
back end. Some things are easier to find based on their names, things like
acquisitions and groups. However, I can't find a table for items in the
catalogue. Also, when creating reports, what is the best way to reference
an image? Is it via url or do I have to convert a binary to image?
Thanks!
-chris
Here’s a report we wrote that does display an image:
https://github.com/cspace-deployment/Tools/blob/master/reports/bampfa/bampfaExhibitionChecklistGroup.jrxml https://github.com/cspace-deployment/Tools/blob/master/reports/bampfa/bampfaExhibitionChecklistGroup.jrxml
It uses a Postgres function:
https://github.com/cspace-deployment/Tools/blob/master/functions/bampfa/get_mediumjpeg_filepath.sql https://github.com/cspace-deployment/Tools/blob/master/functions/bampfa/get_mediumjpeg_filepath.sql
Chris
On Sep 1, 2016, at 9:30 AM, Yousuf Nejati yousuf.nejati@granitehorizon.com wrote:
Here are some sample scripts to help you understand how things are generated, brought to you by the UC Berkeley Django Web Apps:
https://github.com/cspace-deployment/Tools/tree/master/datasources https://github.com/cspace-deployment/Tools/tree/master/datasources
For example (derived from the default Django Web App deployment @ https://github.com/cspace-deployment/cspace_django_project/blob/master/solr/core.public.sql https://github.com/cspace-deployment/cspace_django_project/blob/master/solr/core.public.sql):
-- data extract to provision data-driven elements of the the core public website
SELECT
-- the first field must be the unique id field and it must be called "id"
h1.name http://h1.name/ AS id,
h1.name http://h1.name/ AS csid_s,
regexp_replace(ong.objectname, '^.)''(.)''$', '\1') AS objectname_s,
coc.objectnumber AS objectnumber_s,
coc.numberofobjects AS numberofobjects_s,
coc.computedcurrentlocation AS computedcurrentlocationrefname_s,
regexp_replace(coc.computedcurrentlocation, '^.)''(.)''$', '\1') AS currentlocation_s,
regexp_replace(coc.recordstatus, '^.)''(.)''$', '\1') AS recordstatus_s,
regexp_replace(coc.physicaldescription, E'[\t\n\r]+', ' ', 'g') AS physicaldescription_s,
regexp_replace(coc.contentdescription, E'[\t\n\r]+', ' ', 'g') AS contentdescription_s,
regexp_replace(coc.contentnote, E'[\t\n\r]+', ' ', 'g') AS contentnote_s,
regexp_replace(coc.fieldcollectionplace, '^.)''(.)''$', '\1') AS fieldcollectionplace_s,
regexp_replace(coc.collection, '^.)''(.)''$', '\1') AS collection_s,
sdg.datedisplaydate AS datemade_s,
coc.physicaldescription AS materials_s,
replace(mp.dimensionsummary, '-', ' ') AS measurement_s,
core.updatedat AS updatedat_s
FROM collectionobjects_common coc
JOIN hierarchy h1 ON (h1.id http://h1.id/ = coc.id http://coc.id/)
JOIN misc ON (coc.id http://coc.id/ = misc.id http://misc.id/ AND misc.lifecyclestate <> 'deleted')
LEFT OUTER JOIN hierarchy h2
ON (coc.id http://coc.id/ = h2.parentid AND h2.name http://h2.name/ = 'collectionobjects_common:objectNameList' AND h2.pos = 0)
LEFT OUTER JOIN objectnamegroup ong ON (ong.id http://ong.id/ = h2.id http://h2.id/)
INNER JOIN collectionspace_core core ON coc.id http://coc.id/ = core.id http://core.id/
LEFT OUTER JOIN hierarchy h3
ON (h3.parentid = coc.id http://coc.id/ AND h3.name http://h3.name/ = 'collectionobjects_common:objectProductionDateGroupList' AND h3.pos = 0)
LEFT OUTER JOIN structuredDateGroup sdg ON (h3.id http://h3.id/ = sdg.id http://sdg.id/)
LEFT OUTER JOIN hierarchy h7
ON (h7.parentid = coc.id http://coc.id/ AND h7.name http://h7.name/ = 'collectionobjects_common:measuredPartGroupList' AND h7.pos = 0)
LEFT OUTER JOIN measuredpartgroup mp
ON (h7.id http://h7.id/ = mp.id http://mp.id/)
LEFT OUTER JOIN hierarchy h14
ON (h14.parentid = coc.id http://coc.id/ AND h14.name http://h14.name/ = 'collectionobjects_common:objectProductionPlaceGroupList' AND h14.pos = 0)
;
On Thu, Sep 1, 2016 at 9:00 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. what table stores catalogue items and best practices for
images in reports (toolhater)
2. Re: what table stores catalogue items and best practices for
images in reports (Aron Roberts)
Message: 1
Date: Wed, 31 Aug 2016 23:11:57 +0000 (UTC)
From: toolhater <toolhater@yahoo.com mailto:toolhater@yahoo.com>
To: CollectionSpace Talk List <talk@lists.collectionspace.org mailto:talk@lists.collectionspace.org>
Subject: [Talk] what table stores catalogue items and best practices
for images in reports
Message-ID: <1125413578.2970968.1472685117929@mail.yahoo.com mailto:1125413578.2970968.1472685117929@mail.yahoo.com>
Content-Type: text/plain; charset="utf-8"
Guys,
I'm trying to find out where everything i see on the front end goes in the back end. Some things are easier to find based on their names, things like acquisitions and groups. However, I can't find a table for items in the catalogue. Also, when creating reports, what is the best way to reference an image? Is it via url or do I have to convert a binary to image?
Thanks!
-chris
I set up adding media images in reports a slightly different way for a
client. I created a short web service that listens for requests from the
report script and outputs the media blob via direct API call to the CSpace
server. It's not perfect but it works well in a private network.
https://github.com/OaklandMuseum/reports#media-blobs
It would be nice if there was a standard approach, though.
Jesse
On Thu, Sep 1, 2016 at 12:37 PM, Chris Hoffman chris_h@berkeley.edu wrote:
Here’s a report we wrote that does display an image:
https://github.com/cspace-deployment/Tools/blob/master/reports/bampfa/
bampfaExhibitionChecklistGroup.jrxml
It uses a Postgres function:
https://github.com/cspace-deployment/Tools/blob/master/
functions/bampfa/get_mediumjpeg_filepath.sql
Chris
On Sep 1, 2016, at 9:30 AM, Yousuf Nejati <yousuf.nejati@granitehorizon.
com> wrote:
Here are some sample scripts to help you understand how things are
generated, brought to you by the UC Berkeley Django Web Apps:
https://github.com/cspace-deployment/Tools/tree/master/datasources
For example (derived from the default Django Web App deployment
@ https://github.com/cspace-deployment/cspace_django_project/blob/master/solr/core.public.sql
https://github.com/cspace-deployment/cspace_django_project/blob/master/solr/core.public.sql
):
-- data extract to provision data-driven elements of the the core public
website
SELECT
-- the first field must be the unique id field and it must be called "id"
h1.name AS
id,
h1.name AS
csid_s,
regexp_replace(ong.objectname, '^.)''(.)''$', '\1') AS
objectname_s,
coc.objectnumber AS
objectnumber_s,
coc.numberofobjects AS
numberofobjects_s,
coc.computedcurrentlocation AS
computedcurrentlocationrefname_s,
regexp_replace(coc.computedcurrentlocation, '^.)''(.)''$', '\1') AS
currentlocation_s,
regexp_replace(coc.recordstatus, '^.)''(.)''$', '\1') AS
recordstatus_s,
regexp_replace(coc.physicaldescription, E'[\t\n\r]+', ' ', 'g') AS
physicaldescription_s,
regexp_replace(coc.contentdescription, E'[\t\n\r]+', ' ', 'g') AS
contentdescription_s,
regexp_replace(coc.contentnote, E'[\t\n\r]+', ' ', 'g') AS
contentnote_s,
regexp_replace(coc.fieldcollectionplace, '^.)''(.)''$', '\1') AS
fieldcollectionplace_s,
regexp_replace(coc.collection, '^.)''(.)''$', '\1') AS
collection_s,
sdg.datedisplaydate AS
datemade_s,
coc.physicaldescription AS
materials_s,
replace(mp.dimensionsummary, '-', ' ') AS
measurement_s,
core.updatedat AS
updatedat_s
FROM collectionobjects_common coc
JOIN hierarchy h1 ON (h1.id = coc.id)
JOIN misc ON (coc.id = misc.id AND misc.lifecyclestate <> 'deleted')
LEFT OUTER JOIN hierarchy h2
ON (coc.id = h2.parentid AND h2.name = 'collectionobjects_common:objectNameList'
AND h2.pos = 0)
LEFT OUTER JOIN objectnamegroup ong ON (ong.id = h2.id)
INNER JOIN collectionspace_core core ON coc.id = core.id
LEFT OUTER JOIN hierarchy h3
ON (h3.parentid = coc.id AND h3.name = 'collectionobjects_common:objectProductionDateGroupList'
AND h3.pos = 0)
LEFT OUTER JOIN structuredDateGroup sdg ON (h3.id = sdg.id)
LEFT OUTER JOIN hierarchy h7
ON (h7.parentid = coc.id AND h7.name = 'collectionobjects_common:measuredPartGroupList'
AND h7.pos = 0)
LEFT OUTER JOIN measuredpartgroup mp
ON (h7.id = mp.id)
LEFT OUTER JOIN hierarchy h14
ON (h14.parentid = coc.id AND h14.name = 'collectionobjects_common:
objectProductionPlaceGroupList' AND h14.pos = 0)
;
On Thu, Sep 1, 2016 at 9:00 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. what table stores catalogue items and best practices for
images in reports (toolhater)
2. Re: what table stores catalogue items and best practices for
images in reports (Aron Roberts)
Message: 1
Date: Wed, 31 Aug 2016 23:11:57 +0000 (UTC)
From: toolhater toolhater@yahoo.com
To: CollectionSpace Talk List talk@lists.collectionspace.org
Subject: [Talk] what table stores catalogue items and best practices
for images in reports
Message-ID: 1125413578.2970968.1472685117929@mail.yahoo.com
Content-Type: text/plain; charset="utf-8"
Guys,
I'm trying to find out where everything i see on the front end goes in
the back end. Some things are easier to find based on their names, things
like acquisitions and groups. However, I can't find a table for items in
the catalogue. Also, when creating reports, what is the best way to
reference an image? Is it via url or do I have to convert a binary to image?
Thanks!
-chris