Caché DTI ContentPublisher v7.7.3 and above: dt_cms_schema.SEOStoryLookup SQL goodies!

Update (2013/06/17):

On top of deleting stories from dt_cms_schema.CMSStoryPubTracking and dt_cms_schema.SEOStoryLookup (see below), Craig M. recently discovered that one needs to delete "related" and/or "popular" stories as well.

Find orphaned SEO stories:

select distinct top 100 cmsStoryId as SEO_CMSStoryId from dt_cms_schema.seostorylookup seo where not exists (select pub.CMSStory from dt_cms_schema.CMSStoryPubTracking pub where seo.cmsStoryId = pub.CMSStory) and cmsStoryId is not null;

Find "related" stories with an orphaned SEO:

select distinct cmsStory as Rel_CMSStoryId,relatedStory from dt_cms_schema.cmsstorymapping where cmsStory in (select distinct top 100 cmsStoryId from dt_cms_schema.seostorylookup seo where not exists (select pub.CMSStory from dt_cms_schema.CMSStoryPubTracking pub where seo.cmsStoryId = pub.CMSStory) and cmsStoryId is not null);

Find "popular" stories with an orphaned SEO:

select distinct storyId as Pop_CMSStoryId,storyHits from dt_cms_trace.popular where storyId in (select distinct top 100 cmsStoryId from dt_cms_schema.seostorylookup seo where not exists (select pub.CMSStory from dt_cms_schema.CMSStoryPubTracking pub where seo.cmsStoryId = pub.CMSStory) and cmsStoryId is not null);

Find "mapping" (slotted) stories with an orphaned SEO:

select distinct cmsstory as Map_CMSStoryId from dt_cms_schema.mapping where cmsstory in (select distinct top 100 cmsStoryId from dt_cms_schema.seostorylookup seo where not exists (select pub.CMSStory from dt_cms_schema.CMSStoryPubTracking pub where seo.cmsStoryId = pub.CMSStory) and cmsStoryId is not null);

Update (2013/05/09):

DTI's Craig M. provided the below steps to my co-worker John H.:

In ContentPublisher, unslot the story from the live slots as well as remove it from the unslotted story list at the top of the Section Editor.
Delete the CMSStoryPubTracking table row for that story (example):

SELECT * FROM dt_cms_schema.CMSStoryPubTracking WHERE CMSStoryPubTracking.CMSStory = 29838964;
DELETE FROM dt_cms_schema.CMSStoryPubTracking WHERE CMSStoryPubTracking.CMSStory = 29838964;

Delete the SEOStoryLookup table row for that story (example):

SELECT * FROM dt_cms_schema.SEOStoryLookup WHERE CMSStoryId = 29838964;
DELETE FROM dt_cms_schema.SEOStoryLookup WHERE CMSStoryId = 29838964;

The link is gone, dead, permanently returning a 404! Yay.

Note: The matters for the 2 SQL statements above. There is a dependency from SEO to PubTracking. If you try to delete SEO and there is a PubTracking entry, it should give you a message that it cannot be deleted. So, always delete the entries in PubTracking first.

A new feature has been added to 2013.1 that allows you to find the SEO and delete it. It will do all of the necessary cleanup.

Add an entry to the `SEOStoryLookup` table:

INSERT INTO dt_cms_schema.SEOStoryLookup (cmsStoryId, created, internalStoryURL, seoURL, serverName, urlType, version)
VALUES (123456, getDate(),'/csp/cms/sites/publication/section/index.csp', '/some/funky/url.html', 'localhost', 0, 61)

Where:

created = When entry it was made. Used mainly for troubleshooting/cleanup (avoid searching on this).
internalStoryURL = "Complex" internal URL.
seoURL = The "external" URL (i.e. what the user see's on his/her webpage); it is entirely valid to have multiple external urls poiniting to the same internal url.
serverName = The external server name for this publication (required, but defaults to "localhost").
urlType = What type of lookup is this? Used for GUI (0 = story URL, -1 = section URL).
version = Which version of the URL is this - 0 is first, 1 is 2nd etc (required, but defaults to 0).

Fix a bad `internalSEO`:

Not sure how often you would need this one, but if you ever need to "fix" an internalStoryURL, here's one way of doing it:

UPDATE dt_cms_schema.SEOStoryLookup
SET internalStoryURL = '/csp/cms/sites/publication/section/BAD/story.csp?cid=12345&sid=123&fid=12'
WHERE cmsStoryId = 1234567
AND internalStoryURL = '/csp/cms/sites/publication/section/subsection/story.csp?cid=12345&sid=123&fid=12'

Change an `seoURL`'s `internalStoryURL`:

UPDATE dt_cms_schema.SEOStoryLookup
SET internalStoryURL = '/csp/cms/sites/publication/section/index.csp', version = 3
WHERE serverName = 'site.com'
AND seoURL = '/section/subsection/'

Create a shortcut URI:

INSERT INTO dt_cms_schema.SEOStoryLookup (cmsStoryId, created, internalStoryURL, seoURL, serverName, urlType, version)
VALUES (123456, '2012-08-29 6:54:40', '/csp/cms/sites/publication/section/story.csp?cid=12345&sid=123&fid=12', '/cooooooooooolnessssssssssss/, 'site.com', 0, 7)

Note:

In SEOStoryLookup, version seems tied to the FullStory template ID of the internalStoryURL (need to confirm this).

Note: Original discussion/thread can be found here:

DTI: Lightning-dev (English): How to permanently delete a URL?

mhulse/How to delete a story from Lightning, for keeps.md