Hi Mark, Jacob, you're both correct. bundle.name was used in DSpace up to version 4. DSpace 5 and later moves metadata of DSpace objects to the metadatavalue table. The code in ItemDAOPostgres is clearly wrong, but has been missed, probably because it rarely triggers. I filed a bug report [1] which also has the new query to get bundle name.
[1] https://jira.duraspace.org/browse/DS-2395 Regards, ~~helix84 Compulsory reading: DSpace Mailing List Etiquette https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette On Tue, Jan 6, 2015 at 7:54 PM, Brown, Jacob <[email protected]> wrote: > 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:[email protected]] > Sent: Tuesday, January 06, 2015 12:42 PM > To: Brown, Jacob > Cc: [email protected] > 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='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 <[email protected]> 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 <[email protected]> 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, 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='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='THUMBNAIL' > > AND bundle.bundle_id=bundle2bitstream.bundle_id AND > bundle2bitstream.bitstream_id=bitstream.bitstream_id AND bitstream.name IN > (SELECT name from nameHolder) > > ) SELECT id FROM namedBitstream; > > > > Jacob > > > > From: Mark Ehle [mailto:[email protected]] > Sent: Wednesday, December 10, 2014 11:12 AM > To: [email protected] > 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/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 > [email protected] > https://lists.sourceforge.net/lists/listinfo/dspace-tech > List Etiquette: > https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette ------------------------------------------------------------------------------ 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 [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
