How to find the size of a Spotfire library folder using the Spotfire Server database

How to find the size of a Spotfire library folder using the Spotfire Server database

This article provides an example SQL query to retrieve the size of content in a Spotfire library folder using the content_size of library items that are stored in Spotfire Server database.
 

To retrieve the size of a Spotfire library folder, follow these steps. This is an example for MS SQL Server:

1. Create a view within the database.

CREATE VIEW [dbo].[ListOfLibraryItems]
AS WITH ListOfLibraryItems_1
AS
(SELECT ITEM_ID, TITLE, CAST(TITLE AS VARCHAR(MAX)) AS LIBRARY_PATH, DESCRIPTION, MODIFIED, MODIFIED_BY , CREATED, CREATED_BY, ACCESSED, CONTENT_SIZE, ITEM_TYPE
FROM DBO.LIB_ITEMS
WHERE (TITLE = 'root')
UNION ALL
SELECT
A.ITEM_ID, A.TITLE, CAST(B.LIBRARY_PATH + '/' + A.TITLE AS VARCHAR(MAX)) AS LIBRARY_PATH, A.DESCRIPTION, A.MODIFIED, A.MODIFIED_BY , A.CREATED, A.CREATED_BY, A.ACCESSED, A.CONTENT_SIZE, A.ITEM_TYPE
FROM DBO.LIB_ITEMS AS A
INNER JOIN
ListOfLibraryItems_1
AS B
ON A.PARENT_ID = B.ITEM_ID) SELECT TOP (100) PERCENT ITEM_ID, TITLE, LIBRARY_PATH, DESCRIPTION, MODIFIED, MODIFIED_BY , CREATED, CREATED_BY, ACCESSED, CONTENT_SIZE, ITEM_TYPE,LABEL, DISPLAY_NAME, IS_CONTAINER
FROM ListOfLibraryItems_1
AS A
LEFT OUTER JOIN
DBO.LIB_ITEM_TYPES AS B
ON A.ITEM_TYPE=B.TYPE_ID AND B.DISPLAY_NAME='folder'
ORDER BY LIBRARY_PATH
GO

2. Now run a select statement on the view to retrieve the folder size information. Retrieved rows include default Spotfire folders.

SELECT A.LIBRARY_PATH,A.LABEL, (SELECT SUM(B.CONTENT_SIZE)
FROM dbo.ListOfLibraryItems1 B
WHERE B.LIBRARY_PATH LIKE A.LIBRARY_PATH+'%') AS SIZE
FROM dbo.ListOfLibraryItems1 A
WHERE A.LABEL='folder'

Note: The key tables used in the above query are:

• LIB_ITEMS - A row for each object in the library

• LIB_ITEM_TYPES - A translation of the ITEM_TYPE GUID to an English version (folder, Data Connection, Information Link etc.)

Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by TIBCO to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration.  It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.