Orphan Files Report

Rhythmyx Technical Discussion
1

Is there anyway to run a jcr query or a report for all the File Items that are not being referenced by any page within Percussion.

We have thousands of file content items, and many of them are not being linked by in any page. So in theory they are oprhan files.

thanks
Manvinder

2

You should be able to write an sql query to select all the file content items (contentid) and see if it exists in the psx_objectrelationship table (and where the parent item is not a folder). The resulting set of contentids are the file items that are not being referenced by any page (not counting autoindexers of course)…

3

The technical support views can help with this.

If you are looking at writing your own report, you can use the SQL in the views as a starter.

This is the source for one of the orphan queries.


        -- Finds all items that are not in a folder and have AA relationship(s) to other items.
	-- Results are filtered by community.
	SELECT CS.*
	FROM CONTENTSTATUS CS
	WHERE 
		-- OBJECTTYPE=1 is an item
		CS.OBJECTTYPE=1
		AND CS.CONTENTID NOT IN 
		(
			-- Finds all items that have a folder relationship
			SELECT DISTINCT(OB.DEPENDENT_ID)
			FROM 
				CONTENTSTATUS CS1,
				CONTENTSTATUS CS2,
				PSX_OBJECTRELATIONSHIP OB	
			WHERE 
				CS1.CONTENTID = OB.DEPENDENT_ID
				AND CS1.OBJECTTYPE=1
				AND OB.OWNER_ID = CS2.CONTENTID
				AND CS2.OBJECTTYPE=2
				AND CS.WORKFLOWAPPID = CS1.WORKFLOWAPPID
		)
		AND CS.CONTENTID IN 
		(
			-- Finds all items that have an AA relationship to other items
			SELECT DISTINCT(OB.DEPENDENT_ID)
			FROM 
				CONTENTSTATUS CS1,
				CONTENTSTATUS CS2,
				PSX_OBJECTRELATIONSHIP OB	
			WHERE 
				CS1.CONTENTID = OB.DEPENDENT_ID
				AND CS1.OBJECTTYPE=1
				AND OB.OWNER_ID = CS2.CONTENTID
				AND CS2.OBJECTTYPE=1
				AND CS2.PUBLIC_REVISION=OB.OWNER_REVISION
				AND CS.WORKFLOWAPPID = CS1.WORKFLOWAPPID
		)
		-- Exclude the '/' root folder which is always contentid=1
		AND CS.CONTENTID <> 1;