Should we add Software Recommendations as a migration target?

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackoverflow.com/questions/315521/should-we-add-software-recommendations-as-a-migration-target

Stack Overflow

Q&A for professional and enthusiast programmers

-- create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO

-- change StackExchange.StackOverflow.Meta  to Meta.StackOverflow.StackExchange
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #siteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
go

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_migrations ( [source] nvarchar(250)
                            , dest nvarchar(200)
                            , value int);
                            
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    -- get the migrations for each db
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';    
declare @http nvarchar  = N''http://''
declare @slash nvarchar = N''/''
declare @url nvarchar(250)
exec #siteurl ''' + @db_c_name  + ''', @url output
insert into #all_migrations 
select @url as [source]
     , substring(
          comment
          , charindex(@http, comment) +7
          , charindex(@slash ,comment, 
               charindex(@http, comment) +7)-
                  (charindex(@http, comment) +7)
           ) as dest
       , count(*) as [tot]     
from posthistory 
where posthistorytypeid in (35,36)
group by substring(comment
       , charindex(@http, comment) +7
       , charindex(@slash, comment, 
            charindex(@http, comment) +7)-
               (charindex(@http, comment) +7))
    '
     exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

-- process the results so src and dest strings are equal
declare  mig_c cursor for 
      select  replace(lower(source), '.stackexchange', '') as src
      , replace(
          replace(
              replace(dest, '.stackexchange.com' ,'')
              , '.net', '')
              , '.com', '') as dst
      ,  value
      from #all_migrations

-- result table
create table #final_migrations ( [source] nvarchar(250)
                                , dest nvarchar(250)
                                , value int);
declare @found int
declare @mig_c_src nvarchar(250)
declare @mig_c_dst nvarchar(250)
declare @mig_c_val int

-- remove mirrored rows
-- I couldn't come up with a better solution
open mig_c
fetch next from mig_c into @mig_c_src, @mig_c_dst, @mig_c_val
while(@@FETCH_STATUS = 0)
begin
 
 select @found = count(*) 
   from #final_migrations
   where source = @mig_c_dst
   and dest = @mig_c_src;
   
   print @found   
   print @mig_c_src
   print @mig_c_dst;
    
   if @found > 0
   begin
      -- in theory migration from and to values should be equal
      update #final_migrations set value = value + @mig_c_val
      where source = @mig_c_dst
      and dest = @mig_c_src;
   end
   else
   begin
      insert into #final_migrations values (@mig_c_src, @mig_c_dst, @mig_c_val)
   end
   
   fetch next from mig_c into @mig_c_src, @mig_c_dst, @mig_c_val
end
close mig_c
deallocate mig_c


 select source, dest,  value
from #final_migrations

where source not like 'meta.%'
and source not like 'meta'
and dest not like 'meta.%'
and source not like 'meta'
and (dest  = 'stackoverflow' or source = 'stackoverflow')
order by value desc

drop table #all_migrations
drop table #final_migrations
DROP PROCEDURE #siteurl;

Enter Parameters

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