Time Tracker分析三(用户角色部分1--表与存储过程)

角色权限设置的参考资料：http://www.cnblogs.com/kwklover/archive/2004/06/29/19455.aspx
关于用户角色的BUG：http://www.cnblogs.com/aierong/archive/2005/01/17/93113.html

我们来彻底重构用户角色部分，至于上面的BUG我们就不管了。只是从TT里分离出用户角色部分来重构。
数据库我们还用ASP.NET Time Tracker的数据库。我们要分析一下用户角色部分要用到的数据表和存储过程。

一。数据库中两个表(TT_Users、TT_Roles)  结构如下：
TT_Roles：角色信息表

字段名	类型	含义	备注
RoleID	Int	角色Id	主键（自动编号）
Name	Nvarchar(50)	角色名称	角色名称

TT_Users：用户信息表

字段名	类型	含义	备注
UserID	Int	用户Id	主键（自动编号）
DisplayName	Nvarchar(50)	用户姓名	显示出来的用户名
Password	Nvarchar(50)	密码	采用MD5的加密方式存储
UserName	Nvarchar(50)	登录名（用户Email）	用于登录，并设置成唯一性索引（可防止注册相同的Email，这样设置后当有相同的Email插入时程序就会抛出异常，捕获这个异常就可判断Email是否重复，这样就可以省掉判断Email是否重复的代码）
RoleID	Int	角色Id	关联角色信息表（TT_Roles）

二。数据库中的存储过程
 1.添加用户的存储过程AddUser
  这个存储过程在Users表中增加了一个的新用户，并赋予该用户以适当的权限。 

CREATE     PROCEDURE TT_AddUser
(
    @UserName nvarchar(50),
    @Password nvarchar(50),
    @DisplayName nvarchar(50),
    @RoleID int
)
AS

--验证用户登陆名@UserName是否存在
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName)
BEGIN
--如果用户@UserName不存在，便添加一条记录
INSERT INTO TT_Users
(
    Username, 
    Password, 
    DisplayName,
    RoleID
)
VALUES
(   
    @UserName,
    @Password,
    @DisplayName,
    @RoleID
)
--添加成功，返回当前UserID的值
SELECT
    @@Identity AS UserID   
END
ELSE
    SELECT -2 AS UserID --如果用户@UserName已经存在，添加失败，返回UserID为-2

GO

分析:
=首先CREATE PROCEDURE TT_AddUser的意思是创建一个名为TT_AddUser的存储过程，它和AS中间是传递参数的定义，（）可以有也可以省略。
@UserName nvarchar(50),
@Password nvarchar(50),
@DisplayName nvarchar(50),
@RoleID int
    @定义变量，空格后面是数据类型。nvarchar代表可变长度的Unicode字符串，后面跟的（）里代表字符串的最大长度，int定义整型变量。
    变量之间用 , 隔开。
=IF Not Exists是条件判断,( )里是SELECT查询语句，FROM 后面的TT_Users是用户表，WHERE子句为检索的条件UserName=@UserName，其中，UserName是字段名，@UserName变量名。（）里整句的意思是从表中查询出一个已定义的用户名。这里的BEGIN和ENG语句块跟在IF后面是当IF语句的值为true（真）时，要执行的语句，这里必须注意：BEGIN和END语句必须成对使用，任何一条语句均不能单独使用。BEGIN 语句行后为 Transact-SQL 语句块。最后，END语句行指示语句块结束。
    假如用户名不存在（保证用户名的唯一性），插入当前注册的新用户信息。
    INSERT INTO TT_Users (UserName, Password, DisplayName, RoleID) VALUES (@UserName, @Password, @DisplayName, @RoleID)
    INSERT INTO在TT_Users表中创建新记录，VALUES关键字为表的新记录指定值，它将值或表达式插入到相对应的列中，UserName就是对应的列，@UserName是值，以下依次类推。
    注意：不能撤消执行INSERT INTO查询的操作。作为预防措施，请在执行INSERT INTO 查询前备份数据。
=SELECT @@Identity AS UserID
SQL Server全局变量@@Identity返回插入新行的标识值（即最后插入的值）。若此语句没有影响任何有标识列的表，则 @@Identity 返回 NULL。
=ELSE
    SELECT -2 AS UserID
ELSE代表IF语句的条件不满足，即用户名已存在，返回UserID的值为-2。

2.删除用户的存储过程DeleteUser
  这个过程只是简单的从Users表中删除一个已经存在的用户记录。

CREATE  PROCEDURE TT_DeleteUser
(
    @UserID int
)
AS
DELETE FROM 
            TT_Users
WHERE        
            UserID = @UserID

=其中AS之前的SQL语句，与储过程中AddUser的类似，不再多说。
=DELETE FROM TT_Users WHERE UserID = @UserID
DELETE是删除的意思，这句的意思是从FROM子句指定的TT_Users表中删除@UserID指定的用户记录，WHERE是条件语句，当条件符合WHERE 语句中的表达式，执行删除命令。

3.更新用户的存储过程UpdateUser
  这个存储过程更新了用户信息，并且会返回一适当的值来显示更新成功与否。

 CREATE   PROCEDURE TT_UpdateUser
(
      @UserID int,
      @UserName nvarchar(50),
      @Password nvarchar(50),
      @DisplayName nvarchar(50),
      @RoleID int
)
AS

--Verify that the UserName is not being updated to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName AND UserID<>@UserID)
    BEGIN
        UPDATE 
            TT_Users
        SET UserName=@UserName,
              Password = @Password,
              DisplayName = @DisplayName,
              RoleID = @RoleID
        WHERE 
               UserID=@UserID

        IF (@@Error<>0) GOTO ErrorHandler
        SELECT 
            1 AS retval
    END
-- If UserName is not unique, indicate so with -2
ELSE
    SELECT
        -2 AS retval

IF @@Error<>0
      GOTO ErrorHandler
ELSE
     BEGIN
          RETURN (0)
     END

ErrorHandler:
BEGIN
RETURN (1)
END

=AS之前请参照添加(AddUser)存储过程，已详细介绍。

=Verify that the UserName is not being updated to a UserName that already exists
这句是注释，说明后面的程序的作用是检验要更新的用户名是否已经存在。
    IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName AND UserID<>@UserID)
检查用户名的唯一性，UserName=@UserName AND UserID<>@UserID意指用户名UserName相同而UserID不同的记录。如果用户名唯一执行下面的语句：
BEGIN
UPDATE TT_Users
SET
    UserName=@UserName,
    Password = @Password,
    DisplayName = @DisplayName,
    RoleID = @RoleID
WHERE UserID=@UserID
IF (@@Error<>0) GOTO ErrorHandler
    SELECT 1 AS retval
END
    UPDATE，SET 和WHERE可以作为一个整体来理解，UPDATE 更改表中的数据， SET指定要更新的列或变量名称的列表，WHERE指定条件来限定所更新的行。
    如果UPDATE语句执行成功，则@@ERROR系统变量返回0，程序返回变量retval=1；如果此语句产生错误，则@@ERROR返回错误号≠0。每一个 Transact-SQL 语句完成时，@@ERROR 的值都会改变。GOTO是强制跳转语句，如有错误，跳转到错误处理程序ErrorHandler。
=ELSE
SELECT
    -2 AS retval
如果条件不满足，即用户名不唯一，程序返回变量retval=-2供调用方识别。
=IF @@Error<>0
        GOTO ErrorHandler
    ELSE
    BEGIN
        RETURN (0)
    END
    如果程序执行过程中产生错误，则@@ERROR返回错误号≠0，跳转到错误处理程序ErrorHandler，如果没有错误，返回0。
=ErrorHandler:
    BEGIN
        RETURN (1)
    END
    错误处理程序。系统存储过程返回0值表示成功，返回非零值则表示失败，这里是1。注意：存储过程RETURN 不能返回空值。如果过程试图返回空值将生成警告信息并返回 0 值。

4.用户列表存储过程ListUsers
Time Tracker Starter Kit 的权限管理分为以下三个角色：
·  系统管理员：负责管理使用者、项目。
·  项目经理：可看各项目成员于项目所花费的工时统计报表。
·  项目成员 (顾问)：负责登录于项目所花费的工时。
    这个过程根据用户发出的角色ID（RoleID）来显示用户的名字、id和任务等信息，如果RoleID 等于3（即项目成员(顾问)），这个过程将只显示他自己的用户信息，如果RoleID 等于2（即是项目经理）的话，那么这个过程将显示出他所管理的所有员工信息。最后，如果RoleID等于1（即是管理员）的话，那么这个过程将显示所有用户的信息。

CREATE PROCEDURE TT_ListUsers
(
    @UserID int,
    @RoleID int
)
AS
IF @RoleID = 1
BEGIN
      SELECT 
           UserID, 
           UserName, 
           TT_Users.RoleID,
           TT_Roles.Name 'RoleName'
    FROM 
       TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
END
ELSE IF @RoleID = 2
BEGIN
    SELECT DISTINCT
       TT_Users.UserID,
       TT_Users.UserName,
       TT_Users.RoleID,
       TT_Roles.Name 'RoleName'
    FROM TT_Users
       INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
       INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
       INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
    WHERE @UserID = TT_Projects.ManagerUserID OR @UserID = TT_Users.UserID
END
ELSE
      SELECT
            UserID,
UserName,
TT_Users.RoleID,
TT_Roles.Name 'RoleName'
FROM
TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
WHERE UserID = @UserID

分析：
=这段代码中要注意的是嵌套的IF……ELSE语句
IF @RoleID = 1
    BEGIN
        SQL语句…
    END
ELSE IF @RoleID = 2
    BEGIN
        SQL语句…
    END
ELSE
=如果@RoleID = 1，是管理员，那么这个过程将显示所有用户的信息。执行：
SELECT
    UserID, 
    UserName, 
    TT_Users.RoleID,   
    TT_Roles.Name 'RoleName'
FROM TT_Users 
    INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
 <SELECT语句中没有WHERE条件，所以返回所有用户的信息。
 <返回的字段是：UserID, UserName, TT_Users.RoleID, TT_Roles.Name 'RoleName'，其中字段TT_Roles.Name以别名RoleName返回。
 <TT_Users TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID是表TT_Users和表TT_Roles以TT_Users.RoleID = TT_Roles.RoleID条件进行内连接（INNER JOIN），即返回匹配的所有数据。在视图中可以直观地看到连接的情况。

 =如果@RoleID = 2，是项目经理，则这个过程将显示他自己和项目成员的信息，执行：
SELECT DISTINCT
    TT_Users.UserID,
    TT_Users.UserName,
    TT_Users.RoleID,
    TT_Roles.Name 'RoleName'
FROM TT_Users 
    INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
    INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
    INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
WHERE @UserID = TT_Projects.ManagerUserID OR @UserID = TT_Users.UserID
 <按照WHERE字句的条件，返回项目经理和项目成员的信息。
 <返回的字段是：UserID, UserName, TT_Users.RoleID, TT_Roles.Name 'RoleName'，其中字段TT_Roles.Name以别名RoleName返回。
 <TT_Users 
INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
是3个内联接（INNER JOIN），即返回匹配的所有数据。在视图中可以直观地看到连接的情况。
上面的图是在Time Tracker数据库→视图→单击右键→新建视图中建立的。
ProjectMembers表的主键是复合的主键，它由ProjectID字段和UserID字段共同组成。这个表描述的是用户在项目中的所具有的成员资格等。用户只需要输入他们成为项目成员的开始时间。在设计时，第二个主键不能用单击钥匙图标来定义，只能打开属性窗口，在“索引/键”选项卡中定义。
ProjectMembers表通过ProjectID字段和Projects表进行外部关联，另外它还通过UserID字段和Users表进行外部关联，它的表结构如下图所示：
 
 
5. 存储过程TT_UserLogin
这个过程根据Users 表中存储的认证证书来对用户进行身份鉴别。如果用户输入的email/password复合字段在认证证书中能够找到的话，就能返回用户的UserName。

CREATE  Procedure TT_UserLogin
(
        @UserName  nvarchar(100),
        @Password nvarchar(50)
)
AS

SELECT
        UserName
FROM
        TT_Users
WHERE
        UserName = @UserName
    AND
        Password = @Password
GO

6. 存储过程TT_GetUserByUserName
这个过程根据UserName字段可以得到用户的id和用户权限。

CREATE  PROCEDURE TT_GetUserByUserName
(
     @UserName nvarchar(50)
)
AS
SELECT 
     UserID, 
     UserName, 
     Password,
     RoleID
FROM 
     TT_Users
WHERE 
      UserName = @UserName
GO

7. 存储过程TT_GetUserDisplayName
这个存储过程是根据用户登陆名UserName得到用户显示名DisplayName

create proc TT_GetUserDisplayName
         @Username nvarchar(50)
AS
         SELECT DisplayName FROM TT_Users
         WHERE UserName = @Username
GO

8.  存储过程TT_ListAllRoles
这个过程将从Roles表中返回所有权限及其id号。

CREATE PROCEDURE TT_ListAllRoles
AS
SELECT 
    RoleID, 
    Name
FROM 
    TT_Roles
GO