SQL script for a FunnelWebLog migration

gistfile1.sql

--#############################################################################
--    SETUP
--#############################################################################

use master
go

RESTORE DATABASE [PaulStovellBlog] 
	FROM  DISK = N'D:\PaulStovellBlog - 17 Jan 2011 122801.bak' 
	WITH  FILE = 1,  
	MOVE N'PaulPad-Production' 
		TO N'P:\Databases\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PaulStovellBlog.mdf',  
	MOVE N'PaulPad-Production_log' 
		TO N'P:\Databases\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PaulStovellBlog_1.ldf',  NOUNLOAD,  STATS = 10
go

use [PaulStovellBlog]
go

--#############################################################################
--    MIGRATION SCRIPT
--#############################################################################

create table dbo.Tag (
	[Id] [int] identity(1,1) not null constraint PK_Tag_Id primary key,
	[Name] [nvarchar](50) not null
)
go

create table dbo.TagItem (
	[Id] [int] identity(1,1) not null constraint PK_TagItem_Id primary key,
	[TagId] [int] not null constraint FK_TagItem_TagId foreign key references dbo.Tag(Id),
	[EntryId] [int] not null constraint FK_TagItem_EntryId foreign key references dbo.Entry(Id),
)
go

create function [dbo].[SplitTags]
(
    @input nvarchar(500)
)
returns @tags table ( Tag nvarchar(500) )
as
begin
    if @input is null return
    
    declare @iStart int, @iPos int
    if substring( @input, 1, 1 ) = ','
        set @iStart = 2
    else set @iStart = 1
      
    while 1=1
    begin
        set @iPos = charindex( ',', @input, @iStart );
        
        if @iPos = 0 set @iPos = len(@input) + 1;
			
		if (@iPos - @iStart > 0) 
			
			insert into @tags values (replace(lower(ltrim(rtrim(substring( @input, @iStart, @iPos-@iStart )))), ' ', '-'))
			
        set @iStart = @iPos+1
        
        if @iStart > len( @input ) 
			break
    end
    return
end
go


-- Discover a list of all tags from meta keywords
insert into Tag (Name)
	select distinct(tags.Tag) as Name from dbo.[Entry] e
	cross apply dbo.[SplitTags](e.MetaKeywords) as tags

-- Associate new tags with posts
insert into TagItem (TagId, EntryId)
	select 
		(select Id from Tag where Name = tags.Tag) as TagId,
		e.Id as PostId
	from dbo.[Entry] e
	cross apply dbo.[SplitTags](e.MetaKeywords) as tags


--#############################################################################
--    TESTING
--#############################################################################

select top 3 * from dbo.Tag
select top 3 * from dbo.TagItem
select top 1000 e.Id, e.Title, e.MetaKeywords, t.Name as Tag
	from dbo.Entry e 
	inner join dbo.TagItem ti on ti.EntryId = e.Id
	inner join dbo.Tag t on t.Id = ti.TagId

-- Count of entries per tag
select t.Name as Tag, COUNT(ti.EntryId) as Entries from TagItem ti
	inner join Tag t on t.Id = ti.TagId
	group by t.Name
	order by Entries desc

--#############################################################################
--    SECOND PART OF MIGRATION SCRIPT
--#############################################################################	

-- I normally take care to name constraints, but kept forgetting to do it for defaults, damnit!

declare @defaultConstraintName nvarchar(100)
select @defaultConstraintName = name
	from sys.default_constraints 
	where name like 'DF_%MetaKeywo%'

declare @str nvarchar(200)
set @str = 'alter table dbo.[Entry] drop constraint ' + @defaultConstraintName
exec (@str)

if (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
begin
	alter fulltext index on dbo.[Entry] disable
	alter fulltext index on dbo.[Entry] drop (MetaKeywords)
	alter fulltext index on dbo.[Entry] enable
end

alter table dbo.Entry
	drop column MetaKeywords
go

drop table dbo.FeedItem
go

drop table dbo.Feed
go

drop function dbo.SplitTags