SUNScholar/SQL Tips/5.X

From Libopedia
Jump to navigation Jump to search
BACK TO SQL TIPS
List of titles
SELECT text_value FROM metadatavalue
WHERE metadatavalue.resource_type_id=2
AND metadatavalue.metadata_field_id=64
Search and delete metadata
  • SELECT text_value FROM metadatavalue WHERE resource_type_id=2 AND ... [YourOtherClauses)
  • select resource_id from metadatavalue where resource_type_id=2 and metadata_field_id=86;
  • select resource_id from metadatavalue where resource_type_id=2 and metadata_field_id=78 and (text_value= OR text_value IS NULL);
  • delete from metadatavalue where metadata_field_id=78 and text_value=;
Find items with missing bitstreams
select h.handle, regexp_replace(t.text_value,E'[\r\n\t ]+',' ','g') as title 
from item i2 inner join metadatavalue t on t.resource_id = i2.item_id and t.resource_type_id = 2
inner join metadatafieldregistry mfr on t.metadata_field_id=mfr.metadata_field_id and mfr.element='title' 
inner join handle h on h.resource_id = i2.item_id and h.resource_type_id = 2 
where not exists (select 1 from item2bundle i2b inner join bundle b on i2b.bundle_id = b.bundle_id where i2.item_id = i2b.item_id)
IR Growth by month
SELECT cm.community_id, co.collection_id, substring(mv.text_value,1,7) AS year_month,  count(*) AS num_items_added  
FROM metadatavalue mv, item it, collection co, community cm, community2collection c2c  
WHERE mv.resource_id = it.item_id 
AND it.owning_collection = co.collection_id  
AND co.collection_id = c2c.collection_id  
AND c2c.community_id = cm.community_id  
AND mv.metadata_field_id = 11  
AND it.in_archive  AND text_value >= '2016-02'  
AND text_value < '2016-03'  GROUP BY 1, 2, 3 
ORDER BY 2;


Deleting bitstreams from bundles with names that match a pattern

This query looks for bundles with a text_value matching the pattern 'tiles_%' (a bundle which was created by an old Atmire plugin), and lists the IDs of the bitstreams themselves, along with the name of the bundle containing them.

select bundle2bitstream.bitstream_id, text_value
from metadatavalue 
join bundle on 
bundle.uuid = dspace_object_id 
join bundle2bitstream on 
bundle2bitstream.bundle_id = bundle.uuid 
where text_value LIKE 'tiles_%';

To mark these bitstreams as deleted, run the following:

update bitstream set deleted = 1 where bitstream_id in (
	select bundle2bitstream.bitstream_id
	from metadatavalue 
	join bundle on 
	bundle.uuid = dspace_object_id 
	join bundle2bitstream on 
	bundle2bitstream.bundle_id = bundle.uuid 
	where text_value LIKE 'tiles_%'
)