Found it!
- That is it:
SELECT
handle.handle, metadatavalue.text_value, bitstream.*
FROM
bitstream, metadatavalue, item2bundle, bundle2bitstream, handle
WHERE
handle.resource_id = item2bundle.item_id
AND item2bundle.bundle_id = bundle2bitstream.bundle_id
AND bundle2bitstream.bitstream_id = bitstream.bitstream_id
AND size_bytes < 2000
AND metadatavalue.text_value LIKE '%.pdf'
AND metadatavalue.resource_type_id = 0
AND bitstream.bitstream_id = metadatavalue.resource_id
AND metadata_field_id = (
SELECT metadata_field_id
FROM metadatafieldregistry mfr, metadataschemaregistry msr
WHERE mfr.metadata_schema_id = msr.metadata_schema_id
AND short_id = 'dc'
AND element = 'title'
AND qualifier IS NULL
);
Sample output:
handle | text_value | bitstream_id |
bitstream_format_id | size_bytes | checksum |
checksum_algorithm | internal_id | deleted |
store_number | sequence_id
------------+--------------------------------+--------------+---------------------+------------+----------------------------------+--------------------+-----------------------------------------+---------+--------------+-------------
10923/7372 | 000470274-Texto+Parcial-0.pdf | 24188 |
4 | 567 | de7dc67933af74b45852a2215d3aafbf | MD5
| 14750871164671937117165429606699072375 | f | 0 |
1
10923/7429 | 000471221-Texto+Completo-0.pdf | 24362 |
4 | 569 | 24587ca381cdfd0fc569e716afaf764d | MD5
| 152719227089471130678544999837454837065 | f | 0 |
1
Em quarta-feira, 30 de março de 2016 14:23:08 UTC-3, Michelangelo Viana
escreveu:
>
> Great helix!
>
> That is exactly what I was looking for!
> Just changed the "size_bytes" paramater from "= 569" to "< 2000", so your
> query list another problematic ones, then I found 47 "empty" bitstreams
> (their size varies from 563 to 569 bytes).
> To make it easier to access the record on the UI and replace the PDF file,
> how can I modify your query to also list the handle code that contains
> the bitstream?
>
> All the Best!
> Michelangelo
>
> Em quarta-feira, 30 de março de 2016 10:55:10 UTC-3, helix84 escreveu:
>>
>> Here's the query for DSpace 5:
>>
>> SELECT metadatavalue.text_value, bitstream.*FROM bitstream,
>> metadatavalueWHERE size_bytes = 569AND metadatavalue.resource_type_id = 0AND
>> bitstream.bitstream_id = metadatavalue.resource_idAND metadata_field_id = (
>> SELECT metadata_field_id
>> FROM metadatafieldregistry mfr, metadataschemaregistry msr
>> WHERE mfr.metadata_schema_id = msr.metadata_schema_id
>> AND short_id = 'dc'
>> AND element = 'title'
>> AND qualifier IS NULL);
>>
>>
>>
>>
>> Regards,
>> ~~helix84
>>
>> Compulsory reading: DSpace Mailing List Etiquette
>> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
>>
>>
--
You received this message because you are subscribed to the Google Groups
"DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/dspace-tech.
For more options, visit https://groups.google.com/d/optout.
