Skip to content

Instantly share code, notes, and snippets.

@mhulse

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

Last active October 13, 2015 01:08
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save mhulse/4115443 to your computer and use it in GitHub Desktop.
Save mhulse/4115443 to your computer and use it in GitHub Desktop.
Download ZIP
Caché DTI ContentPublisher v7.7.3 and above: dt_cms_schema.SEOStoryLookup SQL goodies!
Raw
How to delete a story from Lightning, for keeps.md

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment