Sorry, I should have clarified that this was for a DSpace 4.1 setup…
I don’t know anything about the database tables for 5.x, but I just looked at:
https://github.com/DSpace/DSpace/blob/dspace-5.0-rc2/dspace-api/src/main/java/org/dspace/content/dao/ItemDAOPostgres.java
which still references a `bundle.name`, which doesn’t make sense unless a) the
`bundle` table still has a name field, or b) ItemDAO is deprecated.
Jacob
From: Mark Ehle [mailto:marke...@gmail.com]
Sent: Tuesday, January 06, 2015 12:42 PM
To: Brown, Jacob
Cc: DSpace-tech@lists.sourceforge.net
Subject: Re: [Dspace-tech] Query to get ID of thumbnail of primary bitstream
OK, Guys, just call me dense, but I can't make sense of Jacob's query - where
it says, bundle.name<http://bundle.name>='THUMBNAIL' - the bundle table does
not have a name field. What am I missing?
(This is on a DSpace 5.0-rc2 box)
Thanks -
Mark
On Thu, Dec 11, 2014 at 7:08 AM, Mark Ehle
<marke...@gmail.com<mailto:marke...@gmail.com>> wrote:
Thanks, guys! That gives me something to chew on. What I need the query for is
to generate a media rss feed from Dspace.
On Wed, Dec 10, 2014 at 5:16 PM, Brown, Jacob
<j.h.br...@tcu.edu<mailto:j.h.br...@tcu.edu>> wrote:
Hi Mark,
Not sure if what you are using the query for, but if you are using it inside a
Java application, DSpace provides a
[service](https://github.com/DSpace/DSpace/blob/master/dspace-api/src/main/java/org/dspace/content/service/ItemService.java)
to get the primary bitstream’s thumbnail for an item. It’s used like, e.g.:
org.dspace.content.service.ItemService.getThumbnail(context,
item.id<http://item.id>, false)
If you need it outside Java, try the following (sorry for the CTEs, I’m using
SQL snippets from helix84 and the ItemService code and didn’t want to rewrite
everything). It could use some re-writing, but should get you started.
with itemHolder as (
SELECT item_id as id FROM item, handle
WHERE handle.resource_id = item.item_id
AND handle.handle = 'dspace/handle'
),
primaryBitstream as (
SELECT bundle.primary_bitstream_id as id FROM item2bundle, bundle
WHERE item2bundle.item_id in (SELECT id FROM itemHolder) AND
item2bundle.bundle_id=bundle.bundle_id AND
bundle.name<http://bundle.name>='ORIGINAL' LIMIT 1
),
nameHolder as (
SELECT name || '.jpg' as name
FROM bitstream
WHERE bitstream_id IN (SELECT id FROM PrimaryBitstream)
),
namedBitstream as (
SELECT bitstream.bitstream_id as id FROM item2bundle, bundle,
bundle2bitstream, bitstream
WHERE item2bundle.item_id IN (SELECT id FROM itemHolder) AND
item2bundle.bundle_id=bundle.bundle_id AND
bundle.name<http://bundle.name>='THUMBNAIL'
AND bundle.bundle_id=bundle2bitstream.bundle_id AND
bundle2bitstream.bitstream_id=bitstream.bitstream_id AND
bitstream.name<http://bitstream.name> IN (SELECT name from nameHolder)
) SELECT id FROM namedBitstream;
Jacob
From: Mark Ehle [mailto:marke...@gmail.com<mailto:marke...@gmail.com>]
Sent: Wednesday, December 10, 2014 11:12 AM
To: DSpace-tech@lists.sourceforge.net<mailto:DSpace-tech@lists.sourceforge.net>
Subject: [Dspace-tech] Query to get ID of thumbnail of primary bitstream
Folks -
I am in need of a way to query postgres in Dspace to give me the ID of the
primary bitstream's thumbnail ID. I am able to get the primary bitstream ID
from the handle (thanks to
helix84<http://dspace.2283337.n4.nabble.com/template/NamlServlet.jtp?macro=user_nodes&user=211466>,
http://dspace.2283337.n4.nabble.com/Get-item-id-from-bitstream-handle-td4662208.html)
but I don't know where to match up the thumbnail to it.
Thanks!
Mark Ehle
Computer Support Librarian
Willard Library
Battle Creek, MI
------------------------------------------------------------------------------
Dive into the World of Parallel Programming! The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net
_______________________________________________
DSpace-tech mailing list
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
