Errata: There was a bug in the community size query. Below is an updated
version.
Tom
--Size of a community (fixed), 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/1597')
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 community_id --Bugfix
FROM starting_comm --Bugfix
UNION --Bugfix
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;
On Tue, 2017-07-25 at 16:08 -0400, Tom Hutchinson wrote:
> 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.
