Difference between revisions of "SUNScholar/SQL Tips/5.X"

From Libopedia
Jump to navigation Jump to search
 
Line 50: Line 50:
 
To mark these bitstreams as deleted, run the following:
 
To mark these bitstreams as deleted, run the following:
  
  <nowiki>update bitstream set deleted = 1 where bitstream_id in (
+
  <nowiki>update bitstream set deleted = true where uuid in (
select bundle2bitstream.bitstream_id
+
    select bundle2bitstream.bitstream_id
from metadatavalue  
+
    from metadatavalue  
join bundle on  
+
    join bundle on
bundle.uuid = dspace_object_id  
+
    bundle.uuid = dspace_object_id  
join bundle2bitstream on  
+
    join bundle2bitstream on
bundle2bitstream.bundle_id = bundle.uuid  
+
    bundle2bitstream.bundle_id = bundle.uuid  
where text_value LIKE 'tiles_%'
+
    where text_value LIKE 'tiles_%'
)</nowiki>
+
    )</nowiki>
 +
 
 +
Now remove the link between the item and the defunct bundle:
 +
 
 +
<nowiki>delete from item2bundle where item2bundle.bundle_id in(
 +
    select distinct bundle.uuid
 +
    from metadatavalue
 +
    join bundle on 
 +
    bundle.uuid = dspace_object_id
 +
    join bundle2bitstream on 
 +
    bundle2bitstream.bundle_id = bundle.uuid
 +
    where text_value LIKE 'tiles_%');</nowiki>
  
 
And to remove these marked items from the assetstore, run the following as the dspace user:
 
And to remove these marked items from the assetstore, run the following as the dspace user:
  
 
  dspace@host $ /home/dspace/bin/dspace cleanup --verbose
 
  dspace@host $ /home/dspace/bin/dspace cleanup --verbose

Latest revision as of 13:35, 17 January 2019

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 = true where uuid 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_%'
    )

Now remove the link between the item and the defunct bundle:

delete from item2bundle where item2bundle.bundle_id in( 
    select distinct bundle.uuid
    from metadatavalue 
    join bundle on  
    bundle.uuid = dspace_object_id 
    join bundle2bitstream on  
    bundle2bitstream.bundle_id = bundle.uuid 
    where text_value LIKE 'tiles_%');

And to remove these marked items from the assetstore, run the following as the dspace user:

dspace@host $ /home/dspace/bin/dspace cleanup --verbose