博客数据库升级手记（二）：新数据库的建立及简单数据的导入

前面一片文章讲了如何将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

效果如下：

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