Claire,
I have built some reports as a companion to our DSpace 1.7.2 installation.
I am using the following query to find items without original media:
select
i.item_id,
substring(regexp_replace(m64.text_value,E'[\r\n\t ]+',' ','g'),1,80) as
title,
handle
from
item i
inner join
handle on i.item_id = resource_id and resource_type_id = 2
left join
metadatavalue m64 on m64.item_id = i.item_id and m64.metadata_field_id =
64
where not exists
(
select 1
from item2bundle i2b
inner join bundle b
on i2b.bundle_id = b.bundle_id
and b.name = 'ORIGINAL'
where i.item_id = i2b.item_id
)
The following query further refines the test to look for specific format
types. In my installation, these bitstream_format_ids map to text, pdf,
html, word, xsl, ppt.
select
i.item_id,
substring(regexp_replace(m64.text_value,E'[\r\n\t ]+',' ','g'),1,80) as
title,
handle
from
item i
inner join
handle on i.item_id = resource_id and resource_type_id = 2
left join
metadatavalue m64 on m64.item_id = i.item_id and m64.metadata_field_id =
64
where not exists
(
select 1
from item2bundle i2b
inner join bundle b
on i2b.bundle_id = b.bundle_id
and b.name = 'ORIGINAL'
inner join bundle2bitstream b2b on b.bundle_id = b2b.bundle_id
inner join bitstream bit on bit.bitstream_id = b2b.bitstream_id
and bit.bitstream_format_id in (6,4,2,9,5,11,13)
where i.item_id = i2b.item_id
)
Terry
On Fri, Sep 21, 2012 at 5:54 AM, Claire Webster <
[email protected]> wrote:
> Hello,
>
> Is there a way (that doesn't involve too much work) to identify a list
> of items in our dspace installation that do not have any full-text
> documents attached to them (other than the licence.txt)?
>
> Any help/guidance very much appreciated.
>
> kind regards,
> Claire
> --
> Claire Webster (nee Lumber)
> Developer, Library Systems
> University of Bristol
> Email: [email protected]
> Tel: (0117) 928 8668 (internal: 88668)
>
>
> ------------------------------------------------------------------------------
> Got visibility?
> Most devs has no idea what their production app looks like.
> Find out how fast your code is with AppDynamics Lite.
> http://ad.doubleclick.net/clk;262219671;13503038;y?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> DSpace-tech mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>
--
Terry Brady
Applications Programmer Analyst
Lauinger Information Technology
202-687-7053
------------------------------------------------------------------------------
Got visibility?
Most devs has no idea what their production app looks like.
Find out how fast your code is with AppDynamics Lite.
http://ad.doubleclick.net/clk;262219671;13503038;y?
http://info.appdynamics.com/FreeJavaPerformanceDownload.html
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
