Jeff Silvis's Blog

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';

Posted by Jeffrey Silvis at 7:52 PM | Permalink | Comments (0)

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.

Posted by Jeffrey Silvis at 3:55 PM | Permalink | Comments (0)

 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

Posted by Jeffrey Silvis at 4:14 PM | Permalink | Comments (0)

 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'";

Posted by Jeffrey Silvis at 4:37 PM | Permalink | Comments (0)