Possible broken relative links after migration (networkwide)

0

Please login or register to vote for this query.

(click on this box to dismiss)

EOS.IO

Q&A for users and developers of distributed applications built on top of the blockchain operating system, EOS.IO

CREATE TABLE #results (site NVARCHAR(250),
                       postid INT,
                       migrationdate DATETIME,
                       title NVARCHAR(250));
                       
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''', m.PostId AS [Post Link], m.CreationDate,
  COALESCE(p.Title, q.Title)
  FROM ' + QUOTENAME(name) + '.dbo.PostHistory AS m
  INNER JOIN ' + QUOTENAME(name) + '.dbo.PostHistory AS b  
    ON m.PostId = b.PostId
   AND b.PostHistoryTypeId = 2 -- Initial Body
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS p
    ON m.PostId = p.Id
  LEFT OUTER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS q
    ON p.ParentId = q.Id
  WHERE m.PostHistoryTypeId = 36 -- Post Migrated Here
    AND (b.Text LIKE ''%](/q%''
      OR b.Text LIKE ''%](/a%''
      OR b.Text LIKE ''%](/u%''
      OR b.Text LIKE ''%](/p%'')
'
FROM sys.databases
WHERE database_id > 5

-- execute it
EXEC (@sql)

SELECT site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      ))
  + '/q/' + CAST(postid AS NVARCHAR) + '|' + title AS 'Link',
  migrationdate AS 'Migration date'
  FROM #results
  ORDER BY migrationdate DESC;
     
DROP TABLE #results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results