Here’s a link to an old post of mine:
http://forum.percussion.com/showpost.php?p=3673
I’ve got more SQL like that in my notes, when I get a chance I’ll dig them out and post them here. Anyone else got any similiarly useful SQL for investigating and de-crufting a Rhythmyx system?
Andrew.
To list templates against the content types they are allowed to be used with:
SELECT PSX_TEMPLATE.NAME, CONTENTTYPES.CONTENTTYPENAME FROM
CONTENTTYPES INNER JOIN PSX_CONTENTTYPE_TEMPLATE ON CONTENTTYPES.CONTENTTYPEID = PSX_CONTENTTYPE_TEMPLATE.CONTENTTYPEID
INNER JOIN PSX_TEMPLATE ON PSX_TEMPLATE.TEMPLATE_ID = PSX_CONTENTTYPE_TEMPLATE.TEMPLATE_ID
ORDER BY PSX_TEMPLATE.NAME, CONTENTTYPES.CONTENTTYPENAME
To see at a glance which “members” have which roles, and vice versa, without expanding all the nodes in the Rhythmyx Server Administrator:
SELECT PSX_SUBJECTS.NAME AS "MEMBER", PSX_SUBJECTS.TYPE AS "MEMBER_TYPE", PSX_ROLES.NAME AS "ROLE" FROM
PSX_SUBJECTS INNER JOIN PSX_ROLE_SUBJECTS ON PSX_SUBJECTS.ID = PSX_ROLE_SUBJECTS.SUBJECTID
INNER JOIN PSX_ROLES ON PSX_ROLE_SUBJECTS.ROLEID = PSX_ROLES.ID
ORDER BY MEMBER_TYPE, MEMBER
To list all relationships between items of a particular type (e.g. to check before deleting all items of that type):
SELECT CONTENTSTATUS_OWNER.TITLE AS OWNER_TITLE, PSX_OBJECTRELATIONSHIP.OWNER_ID, PSX_OBJECTRELATIONSHIP.DEPENDENT_ID, CONTENTSTATUS_DEPENDENT.TITLE AS DEPENDENT_TITLE
FROM PSX_OBJECTRELATIONSHIP
INNER JOIN CONTENTSTATUS CONTENTSTATUS_DEPENDENT ON PSX_OBJECTRELATIONSHIP.DEPENDENT_ID = CONTENTSTATUS_DEPENDENT.CONTENTID
INNER JOIN CONTENTSTATUS CONTENTSTATUS_OWNER ON PSX_OBJECTRELATIONSHIP.OWNER_ID = CONTENTSTATUS_OWNER.CONTENTID
WHERE CONTENTSTATUS_OWNER.CONTENTTYPEID = 398
AND PSX_OBJECTRELATIONSHIP.OWNER_REVISION = CONTENTSTATUS_OWNER.CURRENTREVISION
ORDER BY PSX_OBJECTRELATIONSHIP.OWNER_ID, PSX_OBJECTRELATIONSHIP.DEPENDENT_ID
Substitute 398 in the above with whatever content type ID you are interested in. Or modify the WHERE clause to search based on something else (e.g. PSX_OBJECTRELATIONSHIP.SLOT_ID, the ID of the slot that the dependent item is in.)
Note, these SQL statements work on Oracle 11g and Rhythmyx 6.5.2. I haven’t tested them on anything else, so you might need to tweak them to work on your system.
and Oracle 10 g…
To find which tables contain a particular field which might be storing your content:
SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = '[I]your_field_name[/I]';
You can obviously use LIKE in the WHERE clause instead of = to widen the search. This is definitely Oracle-only.
To keep track of which templates are the default for which content type and site:
SELECT PSX_TEMPLATE.NAME AS TEMPLATE, CONTENTTYPES.CONTENTTYPENAME AS CONTENT_TYPE, PSX_TEMPLATE.PUBLISHWHEN, RXSITES.SITENAME FROM
CONTENTTYPES INNER JOIN PSX_CONTENTTYPE_TEMPLATE ON CONTENTTYPES.CONTENTTYPEID = PSX_CONTENTTYPE_TEMPLATE.CONTENTTYPEID
INNER JOIN PSX_TEMPLATE ON PSX_TEMPLATE.TEMPLATE_ID = PSX_CONTENTTYPE_TEMPLATE.TEMPLATE_ID
INNER JOIN PSX_VARIANT_SITE ON PSX_VARIANT_SITE.VARIANTID = PSX_TEMPLATE.TEMPLATE_ID
INNER JOIN RXSITES ON RXSITES.SITEID = PSX_VARIANT_SITE.SITEID
ORDER BY PSX_TEMPLATE.NAME, CONTENTTYPES.CONTENTTYPENAME
To find items involved in relationships that you need to delete before you can delete a slot:
SELECT CONTENTSTATUS_OWNER.TITLE AS OWNER_TITLE, PSX_OBJECTRELATIONSHIP.OWNER_ID, PSX_OBJECTRELATIONSHIP.DEPENDENT_ID, CONTENTSTATUS_DEPENDENT.TITLE AS DEPENDENT_TITLE
FROM PSX_OBJECTRELATIONSHIP
INNER JOIN CONTENTSTATUS CONTENTSTATUS_DEPENDENT ON PSX_OBJECTRELATIONSHIP.DEPENDENT_ID = CONTENTSTATUS_DEPENDENT.CONTENTID
INNER JOIN CONTENTSTATUS CONTENTSTATUS_OWNER ON PSX_OBJECTRELATIONSHIP.OWNER_ID = CONTENTSTATUS_OWNER.CONTENTID
WHERE PSX_OBJECTRELATIONSHIP.SLOT_ID = 775
Replace 775 in the above with the ID of the slot you want to delete.
All (except the Oracle-only USER_TAB_COLUMNS one) worked without tweaking on MS SQL Server 2005.
Thank you, Andrew! Very useful queries.
To delete all relationships where the dependent item (i.e. the item in the slot) is of a certain content type:
DELETE FROM PSX_OBJECTRELATIONSHIP WHERE DEPENDENT_ID IN
(SELECT CONTENTID FROM CONTENTSTATUS WHERE CONTENTTYPEID = XXX);
Replace XXX in the above with the Content Type ID you are getting rid of (e.g. 360) Obviously it is better to use the Purge function in the Content Explorer when stripping a system of all items of a certain type, but there can be the odd few items left that will not purge.
The following SQL will find all content types, the workflows that are currently being used by at least one item of that type, and the number of items per type/workflow combination. It cannot find all the optional workflows that a content type may use, but isn’t currently, because that information is (probably) stored in XML files in /Rhythmyx/ObjectStore. That can be accessed, by expanding nodes in the Workflows folder in System Design in the Workbench.
SELECT CONTENTTYPES.CONTENTTYPENAME, WORKFLOWAPPS.WORKFLOWAPPNAME, Count(CONTENTSTATUS.CONTENTID) AS NUM_ITEMS FROM CONTENTSTATUS
LEFT OUTER JOIN WORKFLOWAPPS ON CONTENTSTATUS.WORKFLOWAPPID = WORKFLOWAPPS.WORKFLOWAPPID
RIGHT OUTER JOIN CONTENTTYPES ON CONTENTTYPES.CONTENTTYPEID = CONTENTSTATUS.CONTENTTYPEID
GROUP BY WORKFLOWAPPS.WORKFLOWAPPNAME, CONTENTTYPES.CONTENTTYPENAME
ORDER BY CONTENTTYPES.CONTENTTYPENAME
The following should list all workflow states and the number of items currently in each. Any state or workflow not listed is not being used. Hence this might be useful if you want to delete some states (unless caching on the application server is a factor?)
SELECT WORKFLOWAPPNAME, STATENAME, Count(CONTENTID) AS NUM_ITEMS FROM
(SELECT CONTENTSTATEID || '-' || WORKFLOWAPPID AS STATEANDWORKFLOWID, CONTENTID, WORKFLOWAPPID FROM CONTENTSTATUS) VCONTENTSTATUS
INNER JOIN (SELECT STATEID || '-' || WORKFLOWAPPID AS STATEANDWORKFLOWID, STATENAME FROM STATES) VSTATES
ON VCONTENTSTATUS.STATEANDWORKFLOWID = VSTATES.STATEANDWORKFLOWID
INNER JOIN WORKFLOWAPPS
ON WORKFLOWAPPS.WORKFLOWAPPID = VCONTENTSTATUS.WORKFLOWAPPID
GROUP BY WORKFLOWAPPNAME, STATENAME
ORDER BY WORKFLOWAPPNAME, STATENAME
To find relationships between items in different communities (e.g. to search for what will be broken links, if you are launching two sites from one Rhythmyx system but one is ready to go live weeks or months before the other):
SELECT DISTINCT CONTENTSTATUS_OWNER.TITLE AS OWNER_TITLE, PSX_OBJECTRELATIONSHIP.OWNER_ID, PSX_OBJECTRELATIONSHIP.DEPENDENT_ID, CONTENTSTATUS_DEPENDENT.TITLE AS DEPENDENT_TITLE, CONTENTSTATUS_DEPENDENT.COMMUNITYID AS DEPENDENT_BELONGS_TO FROM PSX_OBJECTRELATIONSHIP
INNER JOIN CONTENTSTATUS CONTENTSTATUS_DEPENDENT ON PSX_OBJECTRELATIONSHIP.DEPENDENT_ID = CONTENTSTATUS_DEPENDENT.CONTENTID
INNER JOIN CONTENTSTATUS CONTENTSTATUS_OWNER ON PSX_OBJECTRELATIONSHIP.OWNER_ID = CONTENTSTATUS_OWNER.CONTENTID
WHERE CONTENTSTATUS_OWNER.COMMUNITYID = ___insert_community_id_here___ AND CONTENTSTATUS_DEPENDENT.COMMUNITYID <> CONTENTSTATUS_OWNER.COMMUNITYID
AND (PSX_OBJECTRELATIONSHIP.OWNER_REVISION = CONTENTSTATUS_OWNER.CURRENTREVISION OR PSX_OBJECTRELATIONSHIP.OWNER_REVISION = CONTENTSTATUS_OWNER.EDITREVISION)
ORDER BY OWNER_ID
To display a simple list of content lists (with the edition, site, publisher and itemfilter used by each) for a particular site:
SELECT RXPUBLISHER.NAME AS PUBLISHERNAME, RXSITES.SITENAME, RXEDITION.DISPLAYTITLE AS EDITIONNAME, RXCONTENTLIST.NAME AS CONTENTLISTNAME, PSX_ITEM_FILTER.NAME AS ITEMFILTERNAME, LEGACY_AUTHTYPE, RXSITES.LASTPUBDATE, RXSITES.LASTJOBID
FROM RXPUBLISHER
INNER JOIN RXSITES ON RXPUBLISHER.PUBLISHERID = RXSITES.PUBLISHERID
INNER JOIN RXEDITION ON RXSITES.SITEID = RXEDITION.DESTSITE
INNER JOIN RXEDITIONCLIST ON RXEDITION.EDITIONID = RXEDITIONCLIST.EDITIONID
INNER JOIN RXCONTENTLIST ON RXEDITIONCLIST.CONTENTLISTID = RXCONTENTLIST.CONTENTLISTID
INNER JOIN PSX_ITEM_FILTER ON RXCONTENTLIST.FILTER_ID = PSX_ITEM_FILTER.FILTER_ID
WHERE RXSITES.SITEID = [I]_your_site_id_here_[/I]
ORDER BY RXEDITION.DISPLAYTITLE, RXCONTENTLIST.NAME
This makes it much easier to spot problems than navigating around the 6.5.2 Publishing user interface. All those tables have more columns, which may be of interest, and listing them may help figure out what they are for (but sadly, for me, most remain as incomprehensible as in the documentation.)