Our DSpace admin recently asked me how to calculate the sizes of collections
and communities. To my surprise, I couldn't immediately find an answer.
I wrote two PostgreSQL queries. To use them:
- optionally, first backup the database - for me that is:
sudo su - postgres -c 'pg_dump -U postgres -f /tmp/db_bkup.sql dspace'
- open a Postgres terminal - for me that is:
sudo su - postgres -c 'psql -U postgres dspace'
- Take one of the queries below, change the handle listed at the beginning,
and paste into the postgres terminal. Be sure to copy the whole thing,
starting with WITH and ending with ;
- When finished, quit the postgres terminal
\q
These are written for DSpace 5. I imagine they would work on DS6. For versions
before 5, they will work if you remove the lines ending with --DS5.
Note: -- signifies the beginning of a line comment in SQL.
Cheers
Tom
--Size of collection with name
--Replace 10066/1597 with the handle for your collection
WITH starting_col (handle) AS (VALUES ('10066/1597'))
SELECT
to_char((sum(bitstream.size_bytes::bigint) )
/ ( 1024 * 1024 * 1024 ), '9999.99' ) || 'GB' AS size
, handle.handle
, text_value --DS5
FROM handle
JOIN starting_col
ON handle.handle = starting_col.handle
JOIN collection
ON handle.resource_id = collection.collection_id
AND handle.resource_type_id = 3
JOIN collection2item
ON collection.collection_id = collection2item.collection_id
JOIN item
ON collection2item.item_id = item.item_id
JOIN item2bundle
ON item.item_id = item2bundle.item_id
JOIN bundle
ON item2bundle.bundle_id = bundle.bundle_id
JOIN bundle2bitstream
ON bundle.bundle_id = bundle2bitstream.bundle_id
JOIN bitstream
ON bundle2bitstream.bitstream_id = bitstream.bitstream_id
JOIN metadatavalue --DS5
ON handle.resource_id = metadatavalue.resource_id --DS5
AND handle.resource_type_id = metadatavalue.resource_type_id --DS5
WHERE metadatavalue.metadata_field_id = 64 --DS5
GROUP BY handle.handle
, text_value --DS5
;
--Size of a community, including sub-communities and collections
--Replace 10066/1597 with the handle for your community
WITH starting_comm AS
(SELECT handle.handle, handle.resource_id AS community_id
FROM handle
WHERE handle.resource_type_id = 4
AND handle = '10066/27')
SELECT size, handle
, text_value AS community --DS5
FROM
(
SELECT
to_char((sum(bitstream.size_bytes::bigint) )
/ ( 1024 * 1024 * 1024 ), '9999.99' ) || 'GB' AS size
FROM
(
WITH RECURSIVE descendants(parent) AS (
SELECT parent_comm_id
FROM community2community
WHERE parent_comm_id = (SELECT community_id FROM starting_comm)
UNION
SELECT community2community.child_comm_id
FROM descendants
JOIN community2community
ON descendants.parent = community2community.parent_comm_id
)
SELECT DISTINCT item_id
FROM descendants
JOIN community2item comm2i
ON descendants.parent = comm2i.community_id
UNION
SELECT DISTINCT item_id
FROM descendants
JOIN community2collection comm2col
ON descendants.parent = comm2col.community_id
JOIN collection2item col2i
ON comm2col.collection_id = col2i.collection_id
) items
JOIN item2bundle
ON items.item_id = item2bundle.item_id
JOIN bundle
ON item2bundle.bundle_id = bundle.bundle_id
JOIN bundle2bitstream
ON bundle.bundle_id = bundle2bitstream.bundle_id
JOIN bitstream
ON bundle2bitstream.bitstream_id = bitstream.bitstream_id
) supersize,
(SELECT *
FROM starting_comm
JOIN metadatavalue --DS5
ON starting_comm.community_id = metadatavalue.resource_id --DS5
AND metadatavalue.resource_type_id = 4 --DS5
AND metadatavalue.metadata_field_id = 64 --DS5
) sc;
--To automatically calculate units, replace these lines:
to_char((sum(bitstream.size_bytes::bigint) )
/ ( 1024 * 1024 * 1024 ), '9999.99' ) || 'GB' AS size
--With these:
CASE
WHEN sum(bitstream.size_bytes::bigint)::bigint
>= (1024::bigint*1024*1024*1024)
THEN to_char(
sum(bitstream.size_bytes::bigint)::bigint
/ (1024::bigint*1024*1024*1024), '9999.99' || 'TB')
WHEN sum(bitstream.size_bytes::bigint)::bigint
>= (1024::bigint*1024*1024)
THEN to_char(
sum(bitstream.size_bytes::bigint)::bigint
/ (1024::bigint*1024*1024), '9999.99' || 'GB')
WHEN sum(bitstream.size_bytes::bigint)::bigint
>= (1024::bigint*1024)
THEN to_char(
sum(bitstream.size_bytes::bigint)::bigint
/ (1024::bigint*1024), '9999.99' || 'MB')
WHEN sum(bitstream.size_bytes::bigint)::bigint >= (1024::bigint)
THEN to_char(
sum(bitstream.size_bytes::bigint)::bigint / (1024::bigint),
'9999.99' || 'KB')
ELSE to_char(sum(bitstream.size_bytes::bigint)::bigint, '9999.99' || 'B')
END AS size
--
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.
