Queries to get size of various things in Cascade

size-queries.sql

# Query to get the file and associated blob data for all Files in a Site and to sum the length of the blobs to get the total byte size for all the files

select fc.id,b.id,octet_length(b.data), (sum(octet_length(b.data))/1024/1024) as "Total Bytes(MB)" from cxml_foldercontent fc left join cxml_blob b on fc.fileBlobId = b.id where siteId='<SITE_ID>' and assetType='FIL' and isCurrentVersion=1 order by octet_length(b.data) desc;

# Same as above, but does not sum and instead lists files in descending order by size

select fc.cachePath, b.id,octet_length(b.data) from cxml_foldercontent fc left join cxml_blob b on fc.fileBlobId = b.id where siteId='9a6807160a00016b00e06bc38171e0d5' and assetType='FIL' and isCurrentVersion=1 order by octet_length(b.data) desc;

# Same as first one except for Page and XML content

select fc.id,x.id,octet_length(x.xmlData), (sum(octet_length(x.xmlData))/1024/1024) as "Total Bytes(MB)"from cxml_foldercontent fc left join cxml_xml x on fc.xmlId = x.id where fc.siteId='9a6807160a00016b00e06bc38171e0d5' and fc.assetType='PAG' and fc.isCurrentVersion=1 and fc.xmlId is not NULL order by octet_length(x.xmlData) desc;

# Same as first one except for Page and SD content

select fc.id,sd.id,octet_length(sd.textData), (sum(octet_length(sd.textData))/1024/1024) as "Total Bytes(MB)" from cxml_foldercontent fc left join cxml_structureddata sd on fc.id = sd.ownerEntityId where fc.siteId='9a6807160a00016b00e06bc38171e0d5' and fc.assetType='PAG' and fc.isCurrentVersion=1 and fc.structuredDataId is not NULL order by octet_length(sd.textData) desc;