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

goodies.md

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)