Jeff Silvis's Blog

Insert dspace type logs into the University of Minho stats addon

INSERT INTO stats.log (date, logger, priority, message) VALUES
('2008-09-19 11:45:22,672', 'org.dspace.app.webui.servlet.DSpaceServlet', 'INFO',
'anonymous:session_id=85E693CBBCBD74DB561B2D2DBEDD0E2B:ip_addr=128.101.29.84:
view_item:handle=2854');

Find the delta t for an item that has a handle 7113

select ('2008-03-07 21:44:01.797-06' - (select item.last_modified from item, handle where handle.resource_id=item.item_id and handle=7113));

Find handles that have been modified since epoch 1197586394

SELECT handle, EXTRACT(EPOCH FROM item.last_modified) from item, handle where handle.resource_id=item.item_id and EXTRACT(EPOCH FROM item.last_modified) > 1197586394 order by handle;

Bitstream from handle

select bundle2bitstream.bitstream_id from item2bundle, handle, bundle2bitstream where (handle.handle=31045 and handle.resource_id = item2bundle.item_id and bundle2bitstream.bundle_id=item2bundle.bundle_id);
bitstream_id
--------------
3976

handle from Bitstream

dspace_sr=> select handle.handle from item2bundle, handle, bundle2bitstream where (bundle2bitstream.bitstream_id=3976 and handle.resource_id = item2bundle.item_id and bundle2bitstream.bundle_id=item2bundle.bundle_id);
handle
--------
31045
(1 row)

Collections that are children of a given community

SELECT handle FROM community2collection, handle WHERE community2collection.collection_id = handle.resource_id AND handle.resource_type_id = 3 AND community2collection.community_id = (SELECT resource_id FROM handle WHERE resource_type_id=4 AND handle=1);

communities that are children of a given community

SELECT handle FROM community2community, handle WHERE community2community.child_comm_id = handle.resource_id AND handle.resource_type_id = 4 AND community2community.parent_comm_id = (SELECT resource_id FROM handle WHERE resource_type_id=4 AND handle=1);