« March 2011 | Main | June 2011 »

April 26, 2011

sql to pull relation metadata

select text_value from metadatavalue, handle where metadata_field_id=40 AND item_id=handle.resource_id AND handle.resource_type_id=2 AND handle.resource_id=item.item_id AND item.withdrawn='f';

April 16, 2011

Pulling handles and bitstreams from dspace & Handles with no bitstreams

I used the sql below to pull pull the bitstream data

[silvi003@strip3 ~]$ cat asset_dump.sql
 select handle.handle, bitstream.name,  bitstream.sequence_id , bitstream.internal_id 
    from handle,item2bundle,bitstream,bundle2bitstream, item where
    handle.resource_id = item2bundle.item_id AND  handle.resource_type_id=2 AND item2bundle.bundle_id=bundle2bitstream.bundle_id AND
    bundle2bitstream.bitstream_id = bitstream.bitstream_id AND bitstream.deleted='f'  AND handle.resource_id=item.item_id AND 
    item.withdrawn='f' order by handle::text::integer;

I then filtered the license.txt bitstreams with:

psql -U dspace_sr dspace_sr < asset_dump.sql | grep -v license.txt > bitstreams.dump

To pull all the live the handles of live items

select handle from handle, item where handle.resource_type_id=2 AND handle.resource_id=item.item_id AND item.withdrawn='f'  order by 
handle::text::integer;

Some items do not have assets

I found 24 of the 44257 handles that have no asset associated with it. These handles are:
7816
7841
9351
9370
9682
9823
9920
10059
10353
19505
20042
21179
21327
21471
25360
25800
28566
44158
44165
44795
44822
48905
51903
61520

If you look in AgEcon:

http://ageconsearch.umn.edu/handle/61520

You find the message:

Files in This Item:

There are no files associated with this item.

April 15, 2011

Example of a dspace item with more than 1 asset

 handle |   name                           | sequence_id           |        internal_id               
--------+------------------------------------------------------------------------------------------------------------------------
 59883  | license.txt                      |     1                 | 14961567638226310543079635595529341271
 59883  | m_term_2010_e.pdf                |     2                 | 71285016412614410518092911718002499005
 59883  | m_term_2010_f.pdf                |     3                 | 33016523793278741265368111818574120226

April 7, 2011

sql to pull dc.description.provenance from dspace database

 select text_value from metadatavalue, handle where metadata_field_id=28 AND item_id=handle.resource_id AND handle.resource_type_id=2 AND handle.resource_id=item.item_id AND item.withdrawn='f';
The piece of sql below is embedded in the provenance sql above and it ensures that the element is an item and has not been withdrawn.
$LIVE_ITEM = " AND handle.resource_type_id=2 AND handle.resource_id=item.item_id AND item.withdrawn='f'";