DSpace 5 vs DSpace 6 Queries

PdfBitstreamsPerCollection.sql

//DSpace 5
select 
  h.handle,
  rp.policy_id,
  bit.sequence_id,
  bitmv.text_value,
  bit.internal_id,
  brp.policy_id
from handle h
join item i   
  on h.resource_id = i.item_id         
  and h.resource_type_id=2      
join resourcepolicy rp
  on rp.resource_id = i.item_id
  and rp.resource_type_id = 2
  and rp.epersongroup_id = 0
join handle ch                   
  on i.owning_collection = ch.resource_id
  and ch.resource_type_id =3
left join item2bundle i2b
  on i.item_id = i2b.item_id
left join metadatavalue bunmv
  on i2b.bundle_id = bunmv.resource_id and bunmv.resource_type_id = 1
  and bunmv.text_value = 'ORIGINAL'
  and i.item_id = i2b.item_id
inner join metadatafieldregistry bunmfr
  on bunmfr.metadata_field_id = bunmv.metadata_field_id
  and bunmfr.element = 'title' and bunmfr.qualifier is null      
inner join bundle2bitstream b2b
  on i2b.bundle_id=b2b.bundle_id
left join bitstream bit
  on b2b.bitstream_id=bit.bitstream_id
left join resourcepolicy brp
  on brp.resource_id=bit.bitstream_id
  and brp.resource_type_id=0
  and brp.epersongroup_id = 0
inner join bitstreamformatregistry bfr
  on bfr.bitstream_format_id = bit.bitstream_format_id
  and bfr.mimetype = 'application/pdf'
inner join metadatavalue bitmv
  on bit.bitstream_id = bitmv.resource_id
  and bitmv.resource_type_id = 0
inner join metadatafieldregistry bitmfr
  on bitmv.metadata_field_id = bitmfr.metadata_field_id
  and bitmfr.element = 'title' and bitmfr.qualifier is null
where ch.handle = :chandle
order by h.handle, bit.sequence_id

//DSpace 6
select 
  h.handle,
  rp.policy_id,
  bit.sequence_id,
  bitmv.text_value,
  bit.internal_id,
  brp.policy_id
from handle h
join item i   
  on h.resource_id = i.uuid    
join resourcepolicy rp
  on rp.dspace_object = i.uuid
inner join epersongroup epg
  on rp.epersongroup_id = epg.uuid
  and name = 'Anonymous'
join handle ch                   
  on i.owning_collection = ch.resource_id
left join item2bundle i2b
  on i.uuid = i2b.item_id
left join metadatavalue bunmv
  on i2b.bundle_id = bunmv.dspace_object_id
  and bunmv.text_value = 'ORIGINAL'
inner join metadatafieldregistry bunmfr
  on bunmfr.metadata_field_id = bunmv.metadata_field_id
  and bunmfr.element = 'title' and bunmfr.qualifier is null      
inner join bundle2bitstream b2b
  on i2b.bundle_id=b2b.bundle_id
left join bitstream bit
  on b2b.bitstream_id=bit.uuid
left join resourcepolicy brp
  on brp.dspace_object=bit.uuid
  and brp.resource_type_id=0
  and brp.epersongroup_id = epg.uuid
inner join bitstreamformatregistry bfr
  on bfr.bitstream_format_id = bit.bitstream_format_id
  and bfr.mimetype = 'application/pdf'
inner join metadatavalue bitmv
  on bit.uuid = bitmv.dspace_object_id
inner join metadatafieldregistry bitmfr
  on bitmv.metadata_field_id = bitmfr.metadata_field_id
  and bitmfr.element = 'title' and bitmfr.qualifier is null
where ch.handle = :chandle
order by h.handle, bit.sequence_id

PdfBitstreamsPerItem.sql

//DSpace 5
select 
  bit.sequence_id,
  bitmv.text_value,
  bit.internal_id,
  brp.policy_id
from handle h
left join item2bundle i2b
  on h.resource_id = i2b.item_id
left join metadatavalue bunmv
  on i2b.bundle_id = bunmv.resource_id and bunmv.resource_type_id = 1
  and bunmv.text_value = 'ORIGINAL'
inner join metadatafieldregistry bunmfr
  on bunmfr.metadata_field_id = bunmv.metadata_field_id
  and bunmfr.element = 'title' and bunmfr.qualifier is null      
inner join bundle2bitstream b2b
  on i2b.bundle_id=b2b.bundle_id
left join bitstream bit
  on b2b.bitstream_id=bit.bitstream_id
left join resourcepolicy brp
  on brp.resource_id=bit.bitstream_id
  and brp.resource_type_id=0
  and epersongroup_id = 0
inner join bitstreamformatregistry bfr
  on bfr.bitstream_format_id = bit.bitstream_format_id
  and bfr.mimetype = 'application/pdf'
inner join metadatavalue bitmv
  on bit.bitstream_id = bitmv.resource_id
  and bitmv.resource_type_id = 0
inner join metadatafieldregistry bitmfr
  on bitmv.metadata_field_id = bitmfr.metadata_field_id
  and bitmfr.element = 'title' and bitmfr.qualifier is null
where h.resource_type_id=2          
  and h.handle = :ihandle
order by bit.sequence_id

//DSpace 6
select 
  bit.sequence_id,
  bitmv.text_value,
  bit.internal_id,
  brp.policy_id
from handle h
left join item2bundle i2b
  on h.resource_id = i2b.item_id
left join metadatavalue bunmv
  on i2b.bundle_id = bunmv.dspace_object_id 
  and bunmv.text_value = 'ORIGINAL'
inner join metadatafieldregistry bunmfr
  on bunmfr.metadata_field_id = bunmv.metadata_field_id
  and bunmfr.element = 'title' and bunmfr.qualifier is null      
inner join bundle2bitstream b2b
  on i2b.bundle_id=b2b.bundle_id
left join bitstream bit
  on b2b.bitstream_id=bit.uuid
left join resourcepolicy brp
  on brp.dspace_object=bit.uuid
inner join epersongroup epg
  on brp.epersongroup_id = epg.uuid
  and name = 'Anonymous'
inner join bitstreamformatregistry bfr
  on bfr.bitstream_format_id = bit.bitstream_format_id
  and bfr.mimetype = 'application/pdf'
inner join metadatavalue bitmv
  on bit.uuid = bitmv.dspace_object_id
inner join metadatafieldregistry bitmfr
  on bitmv.metadata_field_id = bitmfr.metadata_field_id
  and bitmfr.element = 'title' and bitmfr.qualifier is null
where h.resource_type_id=2          
  and h.handle = :ihandle
order by bit.sequence_id