前面一片文章讲了如何将Access数据库直接导入SQL Server。但导入完成后,是强烈建议不要使用的。因为里面的object命名都与sql默认的不符,许多T-SQL行为也很诡异,所以我需要重新建立一个数据库,并且把旧的数据导入到新的数据库中。

一、新数据库的设计

对于新的数据库,这恰好是一次重构和升级的机会,我可以将4年前不合理的表结构设计重构一下(4年前也就是我第一次做这个博客的时候,菜鸟一只)。新的数据库中,博客子系统的设计如下(其实还有很多别的表,但不是给博客主业务用的,以后再讨论):

而旧数据库仅仅只有3张表:Blog、Category、Comment。

对于Category,没有太大的改动,仅仅是增加一个”Name”字段,用来做MVC的路由,而”DisplayName”的功能则和原先数据库是一样的,表示这个分类在页面上显示的名称。

Comment表增加了Email字段,是可选的,用来保存评论者的邮件地址,以便我和他联系。

Blog表重命名为了Post,这是因为概念的转变。Blog应当是系统的总称,里面可以有文章、有留言、以及友情链接、设置等其他数据。而Post,则是描述所有的文章,是博客的主业务对象。

新增的表是Tag、PostTag及BlogAttachment。原先文章的标签都是在Blog.Keywords中以逗号分隔的,由网站程序负责检索和分割。这样显然不符合数据库设计规范(1NF),并且很影响效率。因此我单独抽了两张表,Tag用于保存标签名称。PostTag用于做多对多的关联(一篇文章可以对应很多个标签,而一个标签可以被许多文章使用)。

BlogAttachment保存的是文章的附件。这个功能最早也是在数据库里的,但后来交给了XML管理,其实这样做不太合理,所以这次又把它重新放回数据库了。

二、如何导入数据

建立了新数据库以后,下一步就是要把数据从老的数据库导入到新数据库了。这里面会存在许多要解决的问题。其中一个就是表结构不匹配的问题。如果只是字段名不一样,可以直接在INSERT SELECT语句中替换,而如果结构不一样,就要进一步处理了。

导入的时候也要注意,要从主表开始导数据,再导从表的数据(外键约束很明显)。

在SQL Server中,要跨数据库访问很简单,只要在要查询的对象前面加上数据库名就可以了。比如SELECT * FROM ABC.dbo.Table1,就算这条语句是在DEF数据库执行的,只要你拥有对ABC数据库的访问权限,就可以查询成功。

首先,最容易处理的是分类表(Category),脚本如下:

INSERT INTO Category
  (
    NAME,
    DisplayName
  )
SELECT c.CategoryName,
       c.CategoryName
FROM   BlogSQL.dbo.Category c

这个脚本会把新表的Name和DisplayName都用旧表的CategoryName来填充,新表的主键Id是自从产生的GUID(这不影响Category的导入,但对于从表Post来说,就需要额外处理CategoryId外键)。而更改Name的工作很明显,需要手动完成(毕竟没有哪个数据源中保存了Name)。

接下来要处理的是最Post表。这里有几个问题需要处理。首先,CategoryId不再能直接导入了,因为数据类型从int变成了uniqueidentifier。但我们还是能根据Category的DisplayName找到对应的一个Category,因为DisplayName是不会重复的,没有两个相同的分类。然后再取他的Id。这只需要两句Inner Join就搞定。

INNER JOIN BlogSQL.dbo.Category c
            ON  c.CategoryId = b.CategoryId
INNER JOIN EdiBlog.dbo.Category c2
            ON  c2.DisplayName = c.CategoryName

另外,同样的,旧数据库中文章的Id为int类型,而新数据库中是uniqueidentifier,即GUID。单单插入数据是不影响的,GUID可以用NEWID()自动产生,但在Post的从表Comment中,就会多出一个额外需要处理的问题,即Comment的外键PostId也不能直接导入了。

最后一个问题是,Access数据库中没有GUID对应的类型,因此我保存的都是空GUID作为替代。这个只要在最后将空ID字符串替换成NULL就好了。导入Post的脚本如下:

INSERT INTO Post
  (
    Title,
    Slug,
    PubDate,
    PostContent,
    Author,
    CommentEnabled,
    AttachmentId,
    IsPublished,
    Hits,
    Rators,
    Rating,
    CategoryId
  )
SELECT b.Title,
       b.Slug,
       b.Posttime,
       b.[Content],
       b.Author,
       b.IfReply,
       b.AttachmentId,
       b.IsDraft,
       b.Hits,
       b.Rators,
       b.Rating,
       c2.Id
FROM   BlogSQL.dbo.Blog b
       INNER JOIN BlogSQL.dbo.Category c
            ON  c.CategoryId = b.CategoryId
       INNER JOIN EdiBlog.dbo.Category c2
            ON  c2.DisplayName = c.CategoryName

UPDATE EdiBlog.dbo.Post SET AttachmentId = NULL WHERE AttachmentId = '00000000-0000-0000-0000-000000000000'

下一个表,是Post的从表Comment。和之前处理类型外键一样,用两个Inner Join搞定:

INSERT INTO Comment
  (
    Username,
    Email,
    IPAddress,
    PubDate,
    CommentContent,
    IsPublished,
    PostId
  )
SELECT c.Replyer,
       NULL,
       c.IPAddress,
       c.Posttime,
       c.ReplyContent,
       '1',
       p.Id
FROM   BlogSQL.dbo.Comment c
       INNER JOIN BlogSQL.dbo.Blog b
            ON  b.Id = c.BlogId
       INNER JOIN EdiBlog.dbo.Post p
            ON  p.Title = b.Title

最后要处理的是标签库,即Tag和PostTag两张表。这是本次升级中最麻烦的导入,必须另起一片文章详细论述。所以这篇就不讨论标签库了。有兴趣的读者请留意下一篇文章。

三、新的存储过程

原来用Access的时候,所有的业务操作都是放在程序中的,很影响效率。我的博客是一个internet站点,需要面对大量的并发访问,这些request并不全是人类,还有搜索引擎爬虫及其他内容检索程序,因此效率很关键。而SQL Server的一大好处就是可以利用存储过程,直接把耗时的逻辑在数据库层面解决,因此我就利用了这一特性,将一些业务操作重写到了存储过程里。下面挑选一些介绍。

GetCategoryCountList

这个存储过程做的是统计每个分类下有多少文章。用到了GROUP BY以及COUNT。

CREATE PROCEDURE [dbo].[GetCategoryCountList]
AS
BEGIN
    SELECT [Category].Name,
           [Category].DisplayName,
           COUNT(Post.Id) AS PostCount
    FROM   [Category]
           LEFT JOIN [Post]
                ON  [Post].CategoryId = [Category].Id
    GROUP BY
           [Category].Name,
           [Category].DisplayName
END

执行结果如下:

GetTagCountList

和刚才类似,也是做统计的。它会返回每个标签对应了多少文章。

CREATE PROCEDURE [dbo].[GetTagCountList]
AS
BEGIN
    SELECT t.Name            AS TagName,
           COUNT(pt.PostId)  AS TagCount
    FROM   Tag                  t,
           PostTag              pt
    WHERE  t.Id = pt.TagId
    GROUP BY
           t.Name
END

效果如下:

GetMonthList

返回每年各月份所发表的文章数量。用于做归档页面。

CREATE PROCEDURE [dbo].[GetMonthList]
AS
BEGIN
    SELECT YEAR(p.PubDate)   AS QYear,
           MONTH(p.PubDate)  AS QMonth,
           COUNT(p.Id)       AS QCount
    FROM   Post                 p
    GROUP BY
           YEAR(p.PubDate),
           MONTH(p.PubDate)
END

效果如下:

另一些不重要的存储过程就不介绍了。下一篇将介绍标签库的数据导入。