The following query should be a start. Note that this query is not
filtering for withdrawn items and it is not verifying bitstream permissions.
select
i.item_id,
h.handle,
mvt.text_value,
mva.text_value,
'your_server/bitstream/handle/' || h.handle || '/' || bit.name ||
'?sequence=' || bit.sequence_id
from item i
inner join metadatavalue mvt
on i.item_id=mvt.item_id
inner join metadatafieldregistry mfrt
on mvt.metadata_field_id=mfrt.metadata_field_id
and mfrt.element = 'title' and mfrt.qualifier is null
inner join metadatavalue mva
on i.item_id=mva.item_id
inner join metadatafieldregistry mfra
on mva.metadata_field_id=mfra.metadata_field_id
and mfra.element = 'creator' and mfra.qualifier is null
inner join handle h
on h.resource_id = i.item_id and h.resource_type_id=2
inner join item2bundle i2b
on i2b.item_id = i.item_id
inner join bundle bun
on bun.bundle_id = i2b.bundle_id and bun.name='ORIGINAL'
inner join bundle2bitstream b2b
on b2b.bundle_id = bun.bundle_id
inner join bitstream bit
on bit.bitstream_id = b2b.bitstream_id
On Wed, Apr 22, 2015 at 1:48 PM, Michelangelo M M Viana <[email protected]>
wrote:
> Hi,
>
>
>
> We need a SQL query to list title, author and URL of bitream(s) linked to
> each handle active (public) in database (DSpace 4.2).
>
>
>
> What we need to list is these three columns: title, author, bitstream URL.
>
> eg:
>
> dc.title: O luteranismo desbotado : um estudo sobre as escolas luteranas
> na região metropolitana de Porto Alegre
>
> dc.creator: Matte, Hubert
> <http://tede2.pucrs.br/tede2/browse?type=author&value=Matte%2C+Hubert>
>
> item Bitstream URL:
> http://tede2.pucrs.br/tede2/bitstream/tede/4668/1/419649.pdf
>
>
>
> If item has more than one bitstream, list all in different “rows”, that is
> OK:
>
> Title, author, URL1
>
> Title, author, URL2
>
> Title, author, URLn
>
>
>
> We need compose this list to update our library OPAC MARC “856” field, due
> these items were catalogued linking to “old” URL and need link to DSpace
> URL now.
>
> In our library OPAC these items had MARC 856 linking to another system, eg:
>
> http://tede.pucrs.br/tde_busca/arquivo.php?codArquivo=2444
>
> and after they were imported to DSpace, it gave to the PDF a particular
> new URL, eg:
>
> http://tede2.pucrs.br/tede2/bitstream/tede/3630/1/421846.pdf
>
> and no relationship was stored (old system URL = DSpace URL) after or
> during the process.
>
>
>
> So, our strategy is:
>
> - by having title, author and DSpace URL we will be able to run an update
> in our OPAC (update 856URL = dspace_bistream_URL where author=dspace_author
> and title=dspace_title).
>
> When an item has more than one bitstream (multiple URLs will be listed for
> the same item), we will update our OPAC 856 manually: there are only 60
> cases of items with multiple URLs, among more than 5000 items in our DSpace.
>
>
>
> Thanks in advance,
>
>
>
> Michelangelo
>
>
>
> *Michelangelo Mazzardo Marques Viana* | Support and Development
>
> *Coordinator of Libraries Systems*. Librarian CRB-10/1306
>
> Pontifical Catholic University of Rio Grande do Sul - PUCRS
>
> Ir. Jose Otao Central Library | Porto Alegre, RS, Brazil
>
> +55(51) 3353.4371| [email protected] | http://www.pucrs.br/biblioteca
>
> ____________________________________________________________________
>
> Central Library Virtual Tour: http://www.pucrs.br/biblioteca/visitavirtual
>
> Find us on *Facebook*: http://www.facebook.com/bibliotecapucrs
>
> Follow us on *Twitter*: https://twitter.com/bibliotecapucrs
>
> Know our spaces on *Flickr*: http://www.flickr.com/bibliotecapucrs
>
>
>
>
> ------------------------------------------------------------------------------
> BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
> Develop your own process in accordance with the BPMN 2 standard
> Learn Process modeling best practices with Bonita BPM through live
> exercises
> http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual-
> event?utm_
> source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
> _______________________________________________
> 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
>
--
Terry Brady
Applications Programmer Analyst
Georgetown University Library Information Technology
https://www.library.georgetown.edu/lit/code
425-298-5498 (Seattle, WA)
------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
_______________________________________________
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
