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 professional and enthusiast programmers

-- Known renamed sites, from https://api.stackexchange.com/docs/sites#pagesize=999&filter=!w*vlTok)SMO-Fx9UGp&run=true
-- JS code to generate list: $.ajax('https://api.stackexchange.com/2.2/sites?pagesize=999&filter=!w*vlTok)SMO-Fx9UGp').then( json => console.log( json.items.flatMap( item => { let url = item.site_url.replace(/^https?:\/\//, ''); return (item.aliases || []).map( alias => alias.replace(/^https?:\/\//, '') ).filter( alias => !/^(meta\.\w+|\w+\.meta)\.stack(exchange|overflow)\.com$/.test(alias) ).map( alias => `  ('${url}', '${alias}')` ) } ).sort().join(',\n') ) )

CREATE TABLE #SiteAliases (
  RealName NVARCHAR(255) NOT NULL,
  Alias NVARCHAR(255) NOT NULL
)  
INSERT INTO #SiteAliases VALUES
  ('3dprinting.stackexchange.com', 'threedprinting.stackexchange.com'),
  ('academia.stackexchange.com', 'academics.stackexchange.com'),
  ('alcohol.stackexchange.com', 'beer.stackexchange.com'),
  ('alcohol.stackexchange.com', 'dranks.stackexchange.com'),
  ('apple.stackexchange.com', 'askdifferent.com'),
  ('askubuntu.com', 'ubuntu.stackexchange.com'),
  ('bicycles.stackexchange.com', 'bicycle.stackexchange.com'),
  ('bicycles.stackexchange.com', 'bikes.stackexchange.com'),
  ('bicycles.stackexchange.com', 'cycling.stackexchange.com'),
  ('boardgames.stackexchange.com', 'boardgame.stackexchange.com'),
  ('communitybuilding.stackexchange.com', 'moderator.stackexchange.com'),
  ('communitybuilding.stackexchange.com', 'moderators.stackexchange.com'),
  ('cooking.meta.stackexchange.com', 'meta.seasonedadvice.com'),
  ('cooking.stackexchange.com', 'seasonedadvice.com'),
  ('crypto.stackexchange.com', 'cryptography.stackexchange.com'),
  ('cs.stackexchange.com', 'computerscience.stackexchange.com'),
  ('dsp.stackexchange.com', 'signals.stackexchange.com'),
  ('electronics.stackexchange.com', 'chiphacker.com'),
  ('electronics.stackexchange.com', 'www.chiphacker.com'),
  ('english.stackexchange.com', 'elu.stackexchange.com'),
  ('expatriates.stackexchange.com', 'expats.stackexchange.com'),
  ('gaming.meta.stackexchange.com', 'meta.arqade.com'),
  ('gaming.meta.stackexchange.com', 'meta.thearqade.com'),
  ('gaming.stackexchange.com', 'arqade.com'),
  ('gaming.stackexchange.com', 'thearqade.com'),
  ('gardening.stackexchange.com', 'landscaping.stackexchange.com'),
  ('german.stackexchange.com', 'deutsch.stackexchange.com'),
  ('graphicdesign.stackexchange.com', 'graphicdesigns.stackexchange.com'),
  ('graphicdesign.stackexchange.com', 'graphicsdesign.stackexchange.com'),
  ('homebrew.stackexchange.com', 'brewadvice.com'),
  ('homebrew.stackexchange.com', 'homebrewing.stackexchange.com'),
  ('interpersonal.stackexchange.com', 'interpersonalskills.stackexchange.com'),
  ('ja.stackoverflow.com', 'jp.stackoverflow.com'),
  ('judaism.stackexchange.com', 'mi.yodeya.com'),
  ('judaism.stackexchange.com', 'miyodeya.com'),
  ('judaism.stackexchange.com', 'yodeya.com'),
  ('judaism.stackexchange.com', 'yodeya.stackexchange.com'),
  ('linguistics.stackexchange.com', 'linguist.stackexchange.com'),
  ('math.stackexchange.com', 'mathematics.stackexchange.com'),
  ('math.stackexchange.com', 'maths.stackexchange.com'),
  ('mathoverflow.net', 'mathoverflow.com'),
  ('mathoverflow.net', 'mathoverflow.stackexchange.com'),
  ('mechanics.stackexchange.com', 'garage.stackexchange.com'),
  ('medicalsciences.stackexchange.com', 'health.stackexchange.com'),
  ('money.stackexchange.com', 'basicallymoney.com'),
  ('money.stackexchange.com', 'www.basicallymoney.com'),
  ('movies.stackexchange.com', 'tv.stackexchange.com'),
  ('music.stackexchange.com', 'guitar.stackexchange.com'),
  ('music.stackexchange.com', 'guitars.stackexchange.com'),
  ('patents.meta.stackexchange.com', 'meta.askpatents.com'),
  ('patents.stackexchange.com', 'askpatents.com'),
  ('patents.stackexchange.com', 'askpatents.stackexchange.com'),
  ('photo.stackexchange.com', 'photography.stackexchange.com'),
  ('photo.stackexchange.com', 'photos.stackexchange.com'),
  ('psychology.stackexchange.com', 'cogsci.stackexchange.com'),
  ('pt.stackoverflow.com', 'br.stackoverflow.com'),
  ('pt.stackoverflow.com', 'stackoverflow.com.br'),
  ('ru.meta.stackoverflow.com', 'meta.hashcode.ru'),
  ('ru.stackoverflow.com', 'hashcode.ru'),
  ('ru.stackoverflow.com', 'stackoverflow.ru'),
  ('rus.stackexchange.com', 'russ.hashcode.ru'),
  ('rus.stackexchange.com', 'russ.stackexchange.com'),
  ('scifi.stackexchange.com', 'fantasy.stackexchange.com'),
  ('scifi.stackexchange.com', 'sciencefiction.stackexchange.com'),
  ('security.stackexchange.com', 'itsecurity.stackexchange.com'),
  ('sharepoint.stackexchange.com', 'sharepointoverflow.com'),
  ('sharepoint.stackexchange.com', 'www.sharepointoverflow.com'),
  ('skeptics.stackexchange.com', 'skeptic.stackexchange.com'),
  ('skeptics.stackexchange.com', 'skepticexchange.com'),
  ('softwareengineering.stackexchange.com', 'programmer.stackexchange.com'),
  ('softwareengineering.stackexchange.com', 'programmers.stackexchange.com'),
  ('sound.stackexchange.com', 'socialsounddesign.com'),
  ('sound.stackexchange.com', 'sounddesign.stackexchange.com'),
  ('space.stackexchange.com', 'thefinalfrontier.stackexchange.com'),
  ('spanish.stackexchange.com', 'espanol.stackexchange.com'),
  ('stackoverflow.com', 'facebook.stackoverflow.com'),
  ('stackoverflow.com', 'www.stackoverflow.com'),
  ('stats.stackexchange.com', 'crossvalidated.com'),
  ('stats.stackexchange.com', 'statistics.stackexchange.com'),
  ('unix.stackexchange.com', 'linux.stackexchange.com'),
  ('ux.stackexchange.com', 'ui.stackexchange.com'),
  ('vegetarianism.stackexchange.com', 'veg.stackexchange.com'),
  ('vegetarianism.stackexchange.com', 'vegetarian.stackexchange.com'),
  ('vi.stackexchange.com', 'vim.stackexchange.com'),
  ('video.stackexchange.com', 'avp.stackexchange.com'),
  ('webapps.meta.stackexchange.com', 'meta.nothingtoinstall.com'),
  ('webapps.stackexchange.com', 'nothingtoinstall.com'),
  ('webmasters.stackexchange.com', 'webmaster.stackexchange.com'),
  ('writing.stackexchange.com', 'writer.stackexchange.com'),
  ('writing.stackexchange.com', 'writers.stackexchange.com')

-- Add meta site aliases for renamed SE.com / SO.com subdomains:
INSERT INTO #SiteAliases SELECT
  CASE WHEN RealName LIKE '%.stackexchange.com' OR RealName LIKE '%.stackoverflow.com'
    THEN LEFT(RealName, LEN(RealName) - 17) + 'meta.' + RIGHT(RealName, 17)
    ELSE 'meta.' + RealName  -- for meta.mathoverflow.net and meta.askubuntu.com
  END AS SiteName,
  LEFT(Alias, LEN(Alias) - 17) + 'meta.' + RIGHT(Alias, 17) AS Alias
FROM #SiteAliases
WHERE Alias LIKE '%.stackexchange.com' OR Alias LIKE '%.stackoverflow.com'


-- Compile a table of DB name / hostname pairs:
CREATE TABLE #RawSiteNames (
  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' OR @host LIKE 'meta.%.stackoverflow.com'
    SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.' + RIGHT(@host, 17)

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


-- Apply aliases:
SELECT DBName, COALESCE(RealName, HostName) AS HostName
INTO #SiteNames
FROM #RawSiteNames LEFT JOIN #SiteAliases ON Alias = HostName

-- 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 EXISTS (SELECT 1 FROM #SiteNames WHERE HostName = @host)
            OR EXISTS (SELECT 1 FROM #SiteAliases WHERE Alias = @host) BEGIN
            -- remove www prefix:
            IF @host LIKE 'www.%' SET @host = SUBSTRING(@host, 5, LEN(@host))
            -- convert meta.whatever.stackexchange.com to whatever.meta.stackexchange.com:
            IF @host LIKE 'meta.%.stackexchange.com' OR @host LIKE 'meta.%.stackoverflow.com'
              SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.' + RIGHT(@host, 17)
            -- canonicalize hostname:
            SELECT @host = RealName FROM #SiteAliases WHERE Alias = @host
            -- 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
  'https://' + HostName + '/' + ShortcutKey + '/' + CAST(TargetId AS NVARCHAR) AS [Target Link],
  SourceId AS [Post Link],
  PostTypes.Name AS [Type],
  Posts.Score AS [Score],
  CASE
    WHEN Users.Id IS NULL THEN Posts.OwnerDisplayName
    ELSE 'site://users/' + CAST(Users.Id AS NVARCHAR) + '|' + Users.DisplayName
  END AS [Author],
  Posts.CreationDate AS [Created],
  Posts.LastEditDate AS [Edited],
  Posts.ClosedDate AS [Closed]
FROM #FilteredLinks
  JOIN Posts ON Posts.Id = SourceId
  LEFT JOIN PostTypes ON PostTypes.Id = PostTypeId
  LEFT JOIN Users ON Users.Id = OwnerUserId

fork query

edited oct 12 19

created oct 11 19 Ilmari Karonen