2

Please login or register to vote for this query.

(click on this box to dismiss)

Created for https://math.meta.stackexchange.com/questions/30743/auto-detecting-dead-links-within-the-stack-exchange-network

Q&A for users of Linux, FreeBSD and other Un*x-like operating systems

-- Compile a table of DB name / hostname pairs:
CREATE TABLE #SiteNames (
  DBName NVARCHAR(255) NOT NULL,
  HostName NVARCHAR(255) NOT NULL
)

DECLARE sites CURSOR FOR
  SELECT name FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')

DECLARE @dbname NVARCHAR(255), @host NVARCHAR(255)

OPEN sites
FETCH NEXT FROM sites INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
  -- This gets most hostnames right, except that per-site metas end up
  -- with old pre-HTTPS hostname like meta.whatever.stackexchange.com:
  SET @host = 'com'
  SELECT @host = value + '.' + @host
    FROM STRING_SPLIT(LOWER(@dbname), '.')

  -- Convert per-site meta hostnames to whatever.meta.stackexchange.com:
  IF @host LIKE 'meta.%.stackexchange.com'
  SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.stackexchange.com'
  IF @host LIKE 'meta.%.stackoverflow.com'
    SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.stackoverflow.com'

  -- Fix sites with custom domains and renamed sites:
  IF @host LIKE '%.stackexchange.com' BEGIN
    IF @host LIKE 'ubuntu.%' 
      SET @host = SUBSTRING(@host, LEN('ubuntu.%'), LEN(@host) - LEN('ubuntu.stackexchange.com')) + 'askubuntu.com'
    IF @host LIKE 'mathoverflow.%'
      SET @host = SUBSTRING(@host, LEN('mathoverflow.%'), LEN(@host) - LEN('mathoverflow.stackexchange.com')) + 'mathoverflow.net'

    IF @host LIKE 'audio.%'       SET @host = 'video.' +               RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
    IF @host LIKE 'garage.%'      SET @host = 'mechanics.' +           RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
    IF @host LIKE 'moderators.%'  SET @host = 'communitybuilding.' +   RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
    IF @host LIKE 'photography.%' SET @host = 'photo.' +               RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
    IF @host LIKE 'programmers.%' SET @host = 'softwareengineering.' + RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
  END
  IF @host = 'br.stackoverflow.com' SET @host = 'pt.stackoverflow.com'
  IF @host = 'br.meta.stackoverflow.com' SET @host = 'pt.meta.stackoverflow.com'

  INSERT INTO #SiteNames (DBName, HostName) VALUES (@dbname, @host)
  FETCH NEXT FROM sites INTO @dbname
END
CLOSE sites
DEALLOCATE sites

-- Get current site hostname:
DECLARE @hostname NVARCHAR(255)
SELECT @hostname = HostName FROM #SiteNames WHERE DBName = DB_NAME()


-- Locate posts with potential links to other sites:
CREATE TABLE #CandidatePosts (
  Id INT NOT NULL,
  Body NVARCHAR(MAX) NOT NULL
);

WITH const AS (SELECT
  '<a href="http://' AS http,
  '<a href="https://' AS https,
  '<a href="//' AS noproto,
  '<a href="//' + @hostname + '/' AS localpattern,
  '<a href="//rads.stackoverflow.com/amzn/' AS amazonpattern,
  '<censored xxx="' AS censored
)
INSERT INTO #CandidatePosts
SELECT Id,
  REPLACE(REPLACE(REPLACE(REPLACE(LOWER(Body), http, noproto), https, noproto),
    localpattern, censored), amazonpattern, censored) AS Body
FROM Posts, const
WHERE Body LIKE '%<a href="%.[cn][oe][mt]/[qa]%'

-- Filter out posts with links only to non-SE sites:
DELETE FROM #CandidatePosts
WHERE Body NOT LIKE '%<a href="//%'
  OR (Body NOT LIKE '%.stackexchange.com/[qa]%'
    AND Body NOT LIKE '%[/.]stackoverflow.com/[qa]%'
    AND Body NOT LIKE '%[/.]superuser.com/[qa]%'
    AND Body NOT LIKE '%[/.]serverfault.com/[qa]%'
    AND Body NOT LIKE '%[/.]mathoverflow.net/[qa]%'
    AND Body NOT LIKE '%[/.]askubuntu.com/[qa]%'
    AND Body NOT LIKE '%[/.]stackapps.com/[qa]%'
  )


-- Extract cross-site from post bodies:
CREATE TABLE #CrossSiteLinks (
  SourceId INT NOT NULL,
  HostName NVARCHAR(255) NOT NULL,
  TargetId INT NOT NULL,
  ShortcutKey NCHAR(1) NOT NULL
)

DECLARE @postid INT,
  @postbody NVARCHAR(MAX),
  @segment NVARCHAR(MAX),
  -- @host NVARCHAR(255), already declared above
  @path NVARCHAR(255),
  @qnum NVARCHAR(255),
  @slug NVARCHAR(255),
  @anum NVARCHAR(255)

DECLARE candidates CURSOR FOR
  SELECT Id, Body FROM #CandidatePosts

OPEN candidates
FETCH NEXT FROM candidates INTO @postid, @postbody
WHILE @@FETCH_STATUS = 0 BEGIN

  -- find all links in the post body
  DECLARE segments CURSOR FOR
    SELECT value FROM STRING_SPLIT(@postbody, '<')

  OPEN segments
  FETCH NEXT FROM segments INTO @segment
  WHILE @@FETCH_STATUS = 0 BEGIN

    IF @segment LIKE 'a href="//%"%' BEGIN
      -- extract hostname and URL path, append a bunch of slashes
      SET @segment = SUBSTRING(@segment, LEN('a href="//') + 1, LEN(@segment))
      SET @segment = LEFT(@segment, PATINDEX('%[#?"]%', @segment) - 1) + '/////'
      
      -- extract hostname and path components from URL
      SET @host = LEFT(@segment, CHARINDEX('/', @segment) - 1)
      SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@host) - 1)
      SET @path = LEFT(@segment, CHARINDEX('/', @segment) - 1)
      SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@path) - 1)
      SET @qnum = LEFT(@segment, CHARINDEX('/', @segment) - 1)
      SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@qnum) - 1)
      SET @slug = LEFT(@segment, CHARINDEX('/', @segment) - 1)
      SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@slug) - 1)
      SET @anum = LEFT(@segment, CHARINDEX('/', @segment) - 1)
      
      -- check if components look like an SE question/answer link
      IF @path IN ('q', 'a', 'questions') BEGIN
        IF LEN(@qnum) BETWEEN 1 AND 9 AND PATINDEX('%[^0-9]%', @qnum) = 0 BEGIN
          IF @host LIKE '%.stackexchange.com' OR
             @host LIKE '%.stackoverflow.com' OR
             @host LIKE '%.superuser.com' OR
             @host LIKE '%.serverfault.com' OR
             @host LIKE '%.askubuntu.com' OR
             @host LIKE '%.stackapps.com' OR
             @host LIKE '%.mathoverflow.net' OR
             @host IN (
               'stackoverflow.com', 'superuser.com', 'serverfault.com', 
               'askubuntu.com', 'stackapps.com', 'mathoverflow.net' 
             ) BEGIN
            -- canonicalize host name
            IF @host LIKE 'www.%' SET @host = SUBSTRING(@host, 5, LEN(@host))
            IF @host LIKE 'meta.%.stackexchange.com'
              SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.stackexchange.com'
            IF @host LIKE 'meta.%.stackoverflow.com'
              SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.stackoverflow.com'
            IF @host LIKE '%.stackexchange.com' BEGIN
              IF @host LIKE 'audio.%'       SET @host = 'video.' +               RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
              IF @host LIKE 'garage.%'      SET @host = 'mechanics.' +           RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
              IF @host LIKE 'moderators.%'  SET @host = 'communitybuilding.' +   RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
              IF @host LIKE 'photography.%' SET @host = 'photo.' +               RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
              IF @host LIKE 'programmers.%' SET @host = 'softwareengineering.' + RIGHT(@host, LEN(@host) - CHARINDEX('.', @host))
            END
            IF @host = 'br.stackoverflow.com' SET @host = 'pt.stackoverflow.com'
            IF @host = 'br.meta.stackoverflow.com' SET @host = 'pt.meta.stackoverflow.com'

            -- save link for checking
            IF LEN(@anum) BETWEEN 1 AND 9 AND PATINDEX('%[^0-9]%', @anum) = 0 AND @path = 'questions'
              INSERT INTO #CrossSiteLinks VALUES (@postid, @host, @anum, 'a')
            ELSE
              INSERT INTO #CrossSiteLinks VALUES (@postid, @host, @qnum, LEFT(@path, 1))
          END
        END
      END
    END
    
    FETCH NEXT FROM segments INTO @segment
  END
  CLOSE segments
  DEALLOCATE segments
  
  FETCH NEXT FROM candidates INTO @postid, @postbody
END
CLOSE candidates
DEALLOCATE candidates


-- Loop over other sites, exclude links to existing posts:
SELECT * INTO #FilteredLinks FROM #CrossSiteLinks
WHERE HostName NOT IN (SELECT HostName FROM #SiteNames)

DECLARE sites CURSOR FOR
  SELECT DISTINCT DBName, #SiteNames.HostName
  FROM #SiteNames
    JOIN #CrossSiteLinks ON #SiteNames.HostName = #CrossSiteLinks.HostName
  WHERE DBName <> DB_NAME()

OPEN sites
FETCH NEXT FROM sites INTO @dbname, @hostname
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT * INTO #CurrentSiteLinks FROM #CrossSiteLinks
  WHERE HostName = @hostname

  EXECUTE('
    USE [' + @dbname + ']
    
    INSERT INTO #FilteredLinks
    SELECT #CurrentSiteLinks.* FROM #CurrentSiteLinks
      LEFT JOIN Posts ON Posts.Id = TargetId
    WHERE Posts.Id IS NULL
  ')

  DROP TABLE #CurrentSiteLinks
  FETCH NEXT FROM sites INTO @dbname, @hostname
END
CLOSE sites
DEALLOCATE sites


-- Output results:
SELECT DISTINCT
  SourceId AS [Post Link],
  'https://' + HostName + '/' + ShortcutKey + '/' + CAST(TargetId AS NVARCHAR) AS [Target Link]
FROM #FilteredLinks

view current version

edited oct 11 19

created oct 11 19 Ilmari Karonen