I3S_Tech_Zap.sql

-- I3S Tech Homework Lv.1
-- By Zap

------ My Split Function ---------------------------------------------------------
create function fn_split(@str nvarchar(max),@separator nvarchar(max))
returns @splits table(segment nvarchar(max))
as
begin
	declare @anchor int=CHARINDEX(@separator,@str),@gram int=len(@separator)
	while @anchor>0 and @gram<=len(@str)
	begin
		insert into @splits values(SUBSTRING(@str, 1, @anchor-1))
		set @str=SUBSTRING(@str,@anchor+@gram,len(@str)-@anchor+@gram+1)
		set @anchor=CHARINDEX(@separator,@str)
	end;
	insert into @splits values(@str)
	return;
end

go
------------------------------------------------------------------------------------

-- clear origin data 
update PostList set 
title=REPLACE(title,'"',''),
tags=REPLACE(tags,'"',''),
excerpt=REPLACE(excerpt,'"',''),
createAt=REPLACE(createAt,'"',''),
forumName=REPLACE(forumName,'"',''),
forumAlias=REPLACE(forumName,'"',''),
media=REPLACE(media,'"','')


-- pre-prepare entity
insert into Entity(CName,EName) values
('分類','Category'),
('文章','Post'),
('標籤','Tag'),
('連結','URL')


-- declare vars
declare 
@postid nvarchar(4000),
@title nvarchar(4000),
@tags nvarchar(4000),
@excerpt nvarchar(4000),
@createAt nvarchar(4000),
@forumName nvarchar(4000),
@forumAlias nvarchar(4000),
@media nvarchar(4000),
@id int,
@dt datetime,
@postType int,
@cateType int,
@URLType int,
@tagType int,
@cid int

-- get entity type
select @postType=eid from Entity where ename='Post'
select @cateType=eid from Entity where ename='Category'
select @URLType=eid from Entity where ename='URL'
select @tagType=eid from Entity where ename='Tag'

-- create class (category)
insert into Class(CName,cdes,Type)
select forumAlias,forumName,@cateType from PostList group by forumAlias,forumName


-- create and open cursor
declare c cursor for select * from PostList open c
-- get first data
fetch next from c into @postid,@title,@tags,@excerpt,@createAt,@forumName,@forumAlias,@media

	while @@FETCH_STATUS =0
	begin	
		--convert postid, createAt
		set @id=CONVERT(int,@postid)
		set @dt=CONVERT(datetime,@createAt)
		-- get category id
		select @cid=cid from Class where cname=@forumName
		-- Add a new post and get object id
		insert into Object(type,cname,cdes,since,Status) values(@postType,@title,@excerpt,@dt,@id)
		set @id=@@IDENTITY
		-- Add Co
		insert into CO(cid,oid)values(@cid,@id)

		-- process tags
		declare @tag_ids table(id int)
		insert into object(CName,type) output inserted.oid into @tag_ids
		select t.segment,@tagType from (
		select segment from fn_split(@tags,'$') 
		except select cname from object where type=@tagType) as t
		
		-- orel tags and post
		insert into ORel(OID1,OID2) select @id,id from @tag_ids

		-- process url
		declare @url_ids table(id int)
		insert into object(CName,type) output inserted.oid into @url_ids
		select t.segment,@URLType from (
		select segment from fn_split(@media,'$') 
		except select cname from object where type=@URLType) as t

		-- orel url and post
		insert into ORel(OID1,OID2) select @id,id from @url_ids
	
		fetch next from c into @postid,@title,@tags,@excerpt,@createAt,@forumName,@forumAlias,@media
	end

close c 
deallocate c

go

天才